Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hello,
I have the below data in separate straight tables for AA, BB and CC analysis for 2014
I have to get an aggregated chart to represent the totals of all 3 in total sales and individual sales for AA,BB and CC for respective destinations for 2014.
The Individual totals from the aggregated table for AA,BB and CC are not matching with the Individual tables.
I performed joins and got all the data into one single aggregated qvd and pulling that data in to reports and the numbers don't match.
Attached is my QVW.
Can someone please help me with this.
Thanks!
How are you joining the data to get the aggregated QVD? Can you post your script for generating the file?
AGG:
Load
ApplyMap('Map', [V_Code]) as [AT.TI Code],
[Hotel Travel Date] as [AT. Travel Date],
ApplyMap('Map1', [V_Code]) as [AT.CC Code],
[V_Code] as [AT. V_Code],
sum([Revenue]) as [AT. Revenue],
sum([Sales]) as [AT.Sales]
Resident [Inventory ]
Where Exists([AV_Code], [V_Code])
Group By [V_Code], [Travel Date]
;
Drop Table [Inventory];
outer Join (AGG)
LOAD
[TI Code] as [AT.TI Code],
[TI Date] as [AT. Travel Date],
[TI Sales] as [AT.TI Sales],
[TI Revenue] as [AT.TI Revenue]
FROM
[XXXXX.QVD] (qvd)
Where Exists([TI V_Code], [TI Code])
;
outer Join (AGG)
LOAD
[Travel Date] as [AT. Travel Date],
[V_ID] as [AT.CC Code],
[Sales] as [AT.CC Sales],
[Revenue] as [AT.CC Revenue]
FROM
[YYYY.QVD](qvd)
Where Exists([CC V_Code],[V_ ID]) ;
Sorry, can't really make sense of that without understanding which fields relate to the fields you're talking about in your tables! Any chance you can post your .qvw?
Hi,
I suggest you do Mapping first and then do the aggregation on the table and then create the SUM fields by aggregation by the field name is better way for load the table.
Hope this helps
Regards
Anand
No... It didn't work 😕
Any other suggestions?
Thanks!
Hi,
To join both table together I will do something like this
Hash128 ([AA Sales] & '|' & [BB Sales] & '|' & [CC Sales]) AS %Key_Join
In both table and this will join the table for you.
Hope this helps
Thanks for your help!
DDid that help?
please mark as helpful or answer
I still have the same issue...
I used apply map and outer join to get data into my Aggregated QVD.
The values still don't match or not even close to the values from production.
You need more information to help me with this issue?