Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
rvijayanth
Contributor

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
Honored Contributor III

Re: Help in pivoting data during load

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
Honored Contributor III

Re: Help in pivoting data during load

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

The Generic Load

Partner
Partner

Re: Help in pivoting data during load

Attached the Qlik NPrinting Log Files

Highlighted
Partner
Partner

Re: Help in pivoting data during load

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

Partner
Partner

Re: Help in pivoting data during load

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
Contributor

Re: Help in pivoting data during load

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

rvijayanth
Contributor

Re: Help in pivoting data during load

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