Group Data Across Columns

[alanhoffman]

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..

[LWSChad]

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.

[iren]

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

[aam82]

I would have 3 tables

Orders, Customers, and Items

 

order1 cust1 burger

order 1 cust1 fries

order2 cust2 fries

[iren]

Hello alanhoffman,

 

Can you please clarify how your tables relate between themselves?

 

IREN 

[alanhoffman]

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