Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
can you share a sample data and the expected output ?
Should look like this - For each row in the cross table
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);
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
I didn't quite understand
you're talking about dara like that ?
if not can you share a complete data and the expected output
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
Fantastic thank you
Regards
Robin