topic map with inline table without cross table in QlikView

map with inline table without cross table

Nagaraju_KCS — Fri, 15 Nov 2024 23:30:25 GMT

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!!

Re: map with inline table without cross table

MayilVahanan — Sat, 05 Dec 2020 06:08:35 GMT

HI @Nagaraju_KCS

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

Re: map with inline table without cross table

Dogwoodd — Mon, 07 Dec 2020 06:26:29 GMT

Mapping Table is nothing but a modified version of Vlookup function ... Hierarchy function in Qlikview · CrossTable function in Qlikview ... be a database, excel sheet, resident table, inline table or a QVD file. ... Prj_Domain field and Salary field has been loaded into Employee_info table without using joins.

Re: map with inline table without cross table

marcus_sommer — Mon, 07 Dec 2020 14:04:16 GMT

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