How to import a table where measures are in rows

[JuMo]

Hello

I am loading the following table in my Qlik DB without any problem

However, I also have to load the table hereunder and I am a bit stuck.

Is it possible to load those data without modifying the original file?

Many thanks

[Qrishna]

can you elaborate a lil more please?

[JuMo]

Yes, of course

In the first table, the measures are presented in column and I can create a simple script to import them, like for exemple:

Load
Date,
[Dimension 01],
[Dimension 02],
[Dimension 03],
[KPI 1 Real],
[KPI 2 Real],
[KPI 3 Real]
From ...

Meanwhile in the second table, my measure depend on the row:

• first row, I import [KPI 1 Target] for July
• second row, I import [KPI 2 Target] for July
• third  row, I import [KPI 3 Target] for July
• fourth  row, I import once again [KPI 1 Target] but this time for August

And I had no Idea how to make a script to import this correctly.

And of coursde, I can't modify the data file, and in real life I have almost 20 measures in the file (not just 3)

Hope it clearer 😓

[JuMo · Accepted solution]

I've came with a solution using the prefix CROSSTABLE to "transpose" my fruits columns into rows and then I am using the prefix GENERIC to convert my entity–attribute–value modeled data  into a traditional, normalized relational table structure.

Here is the result:

Here is the script I used

FruitInnit:
CROSSTABLE(Fruit, Sales ,2 )
LOAD 
	Measure,
    Date, 
	Apple , 
	Banana, 
	Strawberry;
SELECT 
	Measure,
	Date,
	Apple,
	Banana,
	Strawberry
FROM GetSheetValues
WITH PROPERTIES (
spreadsheetKey='mykey',
range='Sheet2',
valueRenderOption='FORMATTED_VALUE',
dateTimeRenderOption='SERIAL_NUMBER',
generatedNumberedColumns='false',
skipRows=''
);

Fruit:
GENERIC
LOAD
     Date,     
     Fruit,
     Measure,
     Sales
RESIDENT FruitInnit;
DROP TABLES FruitInnit;

RENAME TABLE "Fruit.KPI 1 Target 1" TO "Fruit";

OUTER JOIN (Fruit)
LOAD * RESIDENT "Fruit.KPI 2 Target 2";
						
OUTER JOIN (Fruit)
LOAD * RESIDENT "Fruit.KPI 3 Target 3";

DROP TABLES "Fruit.KPI 2 Target 2","Fruit.KPI 3 Target 3";

Further doc:

Generic prefix

https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefix...

Cross table prefix

https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefix...