• 0

Group Data Across Columns

Question

Hi there Caspio gurus...

I have a table: Orders..

In this table is a variety of columns holding data in columns:

Orders_Food_Order_1

Orders_Food_Qty_1

Orders_Food_Order_2

Orders_Food_Qty_2

Orders_Drink_Order_1

Orders_Drink_Qty_1

Orders_Drink_Order_2

Orders_Drink_Qty_2

The Food_Order and Drink_Order fields are text and hold different food and drink orders from different customers and may hold the same data in different columns

eg for Food

Order 1       Qty 1    Order 2      Qty 2     Order 1   Qty 1    Order 2   Qty 2

Customer 1:  HotDog          1          Fries             2         Shake      1           Coke       1

Customer 2:  Fries               2         Burger           2

Customer 3:  OnionRings     1        HotDog          1         Coke       1

Customer 4:  Burger             2         OnionRings   1

What I would like to be able to do is aggregate this data so that I can perform calculations on that data - specifically how many orders for each type of food?

ie

Burger: 4

Fries: 4

HotDog: 2

OnionRings: 2

Shake: 1

Coke: 2

Any assistance would be greatly appreciated..

Cheers..

Recommended Posts

• 0

From my understanding you'll need to 1) filter your data by food or drink item each time you want to run a report, or 2) have a separate data page for each food or drink item that automatically filters by a specific food item.

Share on other sites

• 0

Hello alanhoffman,

I would recommend you to create the 'Orders' table:

Customer     Order            Qty

Customer1   HotDog           1

Customer1   Fries               2

Customer1   Shake             1

Customer1   Coke               1

Customer2   Fries               2

Customer2   Burger            2

Customer3   OnionRings    1

Customer3   HotDog           1

Customer3   Coke               1

Customer4   Burger            2

Than you can create Tabular DataPage, group by 'Order' field and add the aggregation field that summarize the values of 'Qty' field and select to calculate aggregation for the first group.

Hope it helps.

IREN

Share on other sites

• 0

I would have 3 tables

Orders, Customers, and Items

order1 cust1 burger

order 1 cust1 fries

order2 cust2 fries

Share on other sites

• 0

Hello alanhoffman,

IREN

Share on other sites

• 0

Thanks for the input to this. It looks like I'll have to reconfigure the structure to make this work efficiently.

I appreciate the support.

Cheers

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.