topic Qlikview Transpose ( Excel to Excel ) using LOAD Script / CrossTable in QlikView

Qlikview Transpose ( Excel to Excel ) using LOAD Script / CrossTable

lakshmanvepuri — Sat, 16 Nov 2024 01:02:58 GMT

HI Gurus,

Please help me to get the Solution as given in the Excel sheet.

I have provided the requirement sheet and also output format sheet.

Requirement:

Please suggest the solution to achieve this.

Output Should be like this:

Thanks a lot.

LK Vepuri

Re: Qlikview Transpose ( Excel to Excel ) using LOAD Script / CrossTable

sunny_talwar — Mon, 16 Mar 2020 12:09:37 GMT

Something like this

Table: CrossTable(Field, tmpValue) LOAD 1 as RowNum, Concat(E, '|') as E, Concat(F, '|') as F, Concat(G, '|') as G, Concat(H, '|') as H, Concat(I, '|') as I, Concat(J, '|') as J FROM [C:\Users\talwars\Downloads\Requirement (1).xlsx] (ooxml, no labels, table is Requirement) Where RecNo() <= 2; MainTable: CrossTable(Field, Value, 3) LOAD B as Country, C as State, D as TELCO, E, F, G, H, I, J FROM [C:\Users\talwars\Downloads\Requirement (1).xlsx] (ooxml, no labels, header is 2 lines, table is Requirement); Left Join (MainTable) LOAD Field, SubField(tmpValue, '|', 1) as MONTH, SubField(tmpValue, '|', 2) as TYPE Resident Table; DROP Table Table; DROP Field Field;

Re: Qlikview Transpose ( Excel to Excel ) using LOAD Script / CrossTable

lakshmanvepuri — Mon, 16 Mar 2020 12:42:56 GMT

Hi Sunny

Thank you very much for the quick response and providing the solution.

But one question... The Month data is not fixed and keep on increasing..

Please help to update the QVW file here so it will be easier to enhance.. Thank You once again,

Thank you

Re: Qlikview Transpose ( Excel to Excel ) using LOAD Script / CrossTable

sunny_talwar — Mon, 16 Mar 2020 13:22:37 GMT

Try this

Table: CrossTable (Field, Field2, 3) LOAD * FROM [C:\Users\talwars\Downloads\Requirement (1).xlsx] (ooxml, no labels, table is Requirement) Where RecNo() <= 2; Table2: LOAD Field, Concat(Field2, '|') as tmpValue Resident Table Group By Field; DROP Table Table; RENAME Table Table2 to Table; MainTable: CrossTable(Field, Value, 3) LOAD B as Country, C as State, D as TELCO, E, F, G, H, I, J FROM [C:\Users\talwars\Downloads\Requirement (1).xlsx] (ooxml, no labels, header is 2 lines, table is Requirement); Left Join (MainTable) LOAD Field, SubField(tmpValue, '|', 1) as MONTH, SubField(tmpValue, '|', 2) as TYPE Resident Table; DROP Table Table; DROP Field Field;

Re: Qlikview Transpose ( Excel to Excel ) using LOAD Script / CrossTable

anushree1 — Mon, 16 Mar 2020 16:11:01 GMT

Try This:

Temp1:

CrossTable (Type,Val,3) LOAD
//Recno() as Num,
Country,
State,
TELCO,
Qty,
"Rev($KK)",
Qty1,
"Rev($KK)1",
Qty2,
"Rev($KK)2"
FROM <<Table Name>>
(ooxml, embedded labels, header is 1 lines, table is Requirement);

//Drop Field Dummy from Temp1;
Temp2:
Load *,
Recno() as Num,
If (WildMatch(Type,'Qty*'),'Qty',
If (WildMatch(Type,'Rev($KK)*'),'Rev($KK)','na')) as Test
Resident Temp1;
Drop Table Temp1;

drop field Type;
Rename Field Test to Type;

Temp3:
CrossTable (ColID,ColDesc,1)
LOAD
ROWNO() as chk,
"E",
F,
G,
H,
"I",
J
FROM <<Table Name>>
(ooxml, no labels, table is Requirement)
where recno()=1;

Fin:

Load Distinct ColDesc
Resident Temp3;
Drop Table Temp3;
Join(Fin)
Load Country , State, TELCO ,Type Resident Temp2;

Fin2:
Load
RowNo() as Num,
ColDesc
Resident Fin;
drop table Fin;
Inner join(Fin2)
Load *

Resident Temp2;

drop table Temp2;