Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I have one scenario, all values should be in single column with label. we can achieve one method using Cross table.
Category | Product | Sales | Offer Sale | with Tax | Extra Sale |
A | A1 | 12000 | 11000 | 13000 | 9000 |
A | A2 | 1300 | 1000 | 2300 | 800 |
A | A3 | 12500 | 10000 | 14000 | 9500 |
A | A4 | 4300 | 2500 | 5000 | 2580 |
A | A5 | 9000 | 7000 | 10000 | 7500 |
B | B1 | 5000 | 2900 | 5000 | 2300 |
B | B2 | 3500 | 1000 | 4000 | 4500 |
like this, but I'm facing issue with performance - data has millions of the records if i do cross join records are increasing more. so completely down my application performance wise.
crosstable (label, value, 2)
LOAD * INLINE [
Category, Product, Sales, Offer Sale, with Tax, Extra Sale
A, A1, 12000, 11000, 13000, 9000
A, A2, 1300, 1000, 2300, 800
A, A3, 12500, 10000, 14000, 9500
A, A4, 4300, 2500, 5000, 2580
A, A5, 9000, 7000, 10000, 7500
B, B1, 5000, 2900, 5000, 2300
B, B2, 3500, 1000, 4000, 4500
];
is there any possibility with out cross join ???
can we map using like inline table to main table columns like below ??
inline table:
Label |
Sales |
Offer Sale |
with Tax |
Extra Sale |
If I select Sales value should be the Sales Amount .. and same for all..
Thanks in Advance!!
HI @qv_testing
You can't map inline table with your fact table without column field. You can try like below
Load * inline
[
Label
Sales
Offer Sale
with Tax
Extra Sale
];
In front end, you can use Label as dimension / listbox and expression like below
pick(match(Label,'Sales','Offer Sale','with Tax','Extra Sale'), Sum([Sales]), Sum([Offer Sale]), Sum([with Tax]), Sum([Extra Sale]))
But again, front end calculation takes times.
If I select Sales value should be the Sales Amount <-- this will satisfy based on above expression
I suggest to consider to load these data with any incremental approach. By small datasets it really doesn't matter if they are completely loaded again and again but by large ones on which then heavy transformations applied a well designed workflow makes a difference.
Beside this if your crosstable-transforming happens with a quite fix structures you may just load the data partly and concatenate them, maybe with something like this:
for each f in 'Sales', 'Offer Sale', 'with Tax', 'Extra Sale'
t: load Category, Product, [$(f)] as [Value], '$(f)' as [Label] from table;
next
- Marcus