Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rvijayanth
Creator
Creator

Help in pivoting data during load

Can someone please help me to make the following transformation in QlikSense?

I like to transform Table 1 to Table 2's format:

TABLE 1:

Company

Type

Value

COMP1

CITY

CITY1

COMP1

CITY

CITY2

COMP1

STATE

STATE1

COMP1

COUNTRY

COUNTRY1

TABLE 2:


Company

CITY

STATE

COUNTRY

COMP1

CITY1

STATE1

COUNTRY1

COMP1

CITY2

STATE1

COUNTRY1

Thanks

7 Replies
Digvijay_Singh

Try this script -

Table1:

Load * inline [

Company,Type,Value

COMP1,CITY,CITY1

COMP1,CITY,CITY2

COMP1,STATE,STATE1

COMP1,COUNTRY,COUNTRY1 ];


Table2:

Generic

Load *

Resident Table1;


//Drop table Table1;


Set vListOfTables = ;


   For vTableNo = 0 to NoOfTables()


      Let vTableName = TableName($(vTableNo)) ;


      If Subfield(vTableName,'.',1)='Table2' Then


         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;


      End If


   Next vTableNo



   CombinedGenericTable:


   Load distinct Company Resident Table1;


   For each vTableName in $(vListOfTables)


      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];


      Drop Table [$(vTableName)];


   Next vTableName

  

   Drop table Table1;




Capture.PNG

Digvijay_Singh

For more details you can go through this blog from HIC -

The Generic Load

marcos_herrera
Partner - Creator III
Partner - Creator III

Attached the Qlik NPrinting Log Files

marcos_herrera
Partner - Creator III
Partner - Creator III

Sorry for the last wrong answer, please see the attachement QVF with the solution using Generic Load

santho_ak
Partner - Creator III
Partner - Creator III

Hi rvijayanth Have you tried pivoting tables through Data Manager. That would be a easy solution.

DataManager-> Edit the table (from bottom)-> Select the column you want to pivot or unpivot.

rvijayanth
Creator
Creator
Author

That did not work for me. I have to try the solutions provided here.

rvijayanth
Creator
Creator
Author

Thanks for the response. Will let you know how it goes.