CrossTable

[8Gates]

Morning all, have a specific cross table query need some pointers on.

Data Set contains pricing data - loaded as below

[Pricing]:
LOAD
[UID] as [Pricing_UID],
[Rental Group] as [Pricing_Rental Group],
[Rental Sub Group] as [Pricing_Rental Sub Group],
[Sub Group] as [Pricing_Sub Group],
[Make] as [Pricing_Make],
[Model] AS [Pricing_Model],
[Derivative] as [Pricing_Derivative],
[CAP ID] as [Pricing_CAP ID],
[CAP ID] as [CAP Key],
[Options Included] as [Pricing_Options],
[A/C] as [Pricing_AC],
[B/T] as [Pricing_BT],
[Term] as [Pricing_Term],
[10] as [Pricing_10],
[20] as [Pricing_20],
[30] as [Pricing_30],
[40] as [Pricing_40],
[50] as [Pricing_50],
[60] as [Pricing_60],
[70] as [Pricing_70],
[80] as [Pricing_80],
[90] as [Pricing_90],
[100] as [Pricing_100]
FROM [lib://DataFiles/Combined Pricebook 2019.xlsx]
(ooxml, embedded labels, table is Combined);

Looking for a Singular table that contains the combined Price Data into one Column (i.e [10], [20], [30] etc into one column - Price - and also identify the pricing type ([10] or [20] etc

Each row may contain all variations on price [10] and [20] and [30] etc

But also need to append the Cap_Key, and Term.

Thanks in advance

Robin

Business Question I need to ask in the combined Max, Min and Average values across all prices by Term.

[Taoufiq_Zarra]

can you share a sample data and the expected output ?

[8Gates]

Should look like this  - For each row in the cross table

[Taoufiq_Zarra]

attached qvf file :

CrossTable(PriceVariation, Price, 13)

LOAD
   [UID] as [Pricing_UID],
[Rental Group] as [Pricing_Rental Group],
[Rental Sub Group] as [Pricing_Rental Sub Group],
[Sub Group] as [Pricing_Sub Group],
[Make] as [Pricing_Make],
[Model] AS [Pricing_Model],
[Derivative] as [Pricing_Derivative],
[CAP ID] as [Pricing_CAP ID],
[CAP ID] as [CAP Key],
[Options Included] as [Pricing_Options],
[A/C] as [Pricing_AC],
[B/T] as [Pricing_BT],
[Term] as [Pricing_Term],
[10] as [Pricing_10],
[20] as [Pricing_20],
[30] as [Pricing_30],
[40] as [Pricing_40],
[50] as [Pricing_50],
[60] as [Pricing_60],
[70] as [Pricing_70],
[80] as [Pricing_80],
[90] as [Pricing_90],
[100] as [Pricing_100]
FROM [lib://Downloads/Combined Pricebook 2019.xlsx]
(ooxml, embedded labels, table is Feuil1);

[8Gates]

Thank you.

If I wish to keep both Cross table and original table - whats the load sequencing?

So Ideally - I want the full pricing table - and also a cross table to reference the data for min/max across all ?

Regards

Robin

[Taoufiq_Zarra]

I didn't quite understand

you're talking about dara like that ?

if not can you share a complete data and the expected output

[8Gates]

Hi , sorry - cross table was great - but also need to retain the original pricing table.

So table 1 - Cross Table

Table 2 pricing table

Need to ensure that the columns to not create duplicate keys .

Regards

Robin

[8Gates]

Fantastic thank you

Regards

Robin