Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello friends. I am dealing with 90+ millions of records and it is quite big application. Users have particular requirements where they want the report to be exactly like a xls sheet. I am able to that exactly but problem is I have to aggreagte and concatenate data again and again to bring it to this desitred format.
I have prepared a sample data and chart.Here, Product dimension has only 3 values (Clother, Shoes and Watches). Users wants another information in in the same pivot table as shown. I am able to achieve this but I hav to agregate and concatenate data again and again which is making my qvw size much bigger (3-4 times larger because of concatenation.
Is there a more elegant way to achieve this? I am attaching sample qvw and xls data to give more information.
Appreciate your help and time.
Also, I am doing aggregation and concatenation in script. Since I am dealing with lot of records, I would prefer an approach which is at back end.
Hi,
May be like this.. Not sure about the performance. Check and Let us know
DIRECTORY;
T1:
CrossTable(Type, Data, 😎
LOAD Product,
SubPrd,
Loc,
Country,
State,
Zip,
Group,
[Super Area],
Amout,
Discount,
Other
FROM
[Sample Xls Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
Concatenate(T1)
LOAD 'Total Products Summary' as Type,
Product,
SubPrd,
Loc,
Country,
State,
Zip,
Group,
[Super Area],
Data
Resident T1 Where Type='Amout';
NoConcatenate
Final:
LOAD if(Type='Other','Others/Extras',
if(Type='Discount','Discounts Applied',
If(Type='Amout',Product,Type))) as Product,
SubPrd,
Loc,
Country,
State,
Zip,
Group,
[Super Area],
Data as Measure
Resident T1;
DROP Table T1;
Hello periasamy. Thanks for your response.
This is a good workaround if you have a smaller data set. In our data model, we have 40+ dimensions and more than 20 different measure which makes it difficult to follow your approach. I tested it and there was hardly any impact on performance.
I appreciate your response and time.
Let me put this into a little different way. Please consider this scenario.
I have different measure for different products e.g MILK_COST,SHOE_COST,CLOTH_COST,SHIRT_COST,WATER_COST, RING_COST, PH_COST etc and 10 more cost.
In my database, there is no such column/field which constitutes all different products. So, I have to create all the these values as Synthetic Dimension and aggregate in pivot table. I am using PICK-MATCH combination in my expression to use correct measure against dimension.
But this is not an elegant approach as I am having millions of rows of data. Do you guys know if there is some better approach at script level so that I can avoid using synthetic dimension at front end?
Thanks for help.