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

[lakshmanvepuri]

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

[sunny_talwar]

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;

[lakshmanvepuri]

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

[sunny_talwar · Accepted solution]

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;

[anushree1 · Accepted solution]

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;