Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rvijayanth
New Contributor III

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

marcos_herrera
Contributor

Re: Help in pivoting data during load

Attached the Qlik NPrinting Log Files

marcos_herrera
Contributor

Re: Help in pivoting data during load

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

sashokku
Contributor

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

Re: Help in pivoting data during load

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

rvijayanth
New Contributor III

Re: Help in pivoting data during load

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

Community Browser