Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Convert vertical columns to Horizontal

Hi,

I have the data in vertical rows which i like to convert to horizontal data columns

Below is the screenshot of the sample of the input and the required output

The data is dynamically retrieved from the table and I like this conversion through load script

Can you pl provide some inputs in cross table usage in loadscript for this conversion

Screen Shot 2018-09-01 at 11.00.53 AM.png

Thanks

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

Hi, Umashankar. To achieve this you can use Generic LOAD

GenTable: Generic Load NBR, Group, Count Resident InputTable;

ResultTable: LOAD Distinct NBR Resident InputTable;


FOR i = 0 to NoOfTables()

  TableList:

  LOAD TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'GenTable.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

NEXT i

DROP TABLES TableList, InputTable;

View solution in original post

6 Replies
Highlighted
Partner
Partner

Hi Umashakar,

Try to using pivot table you will get same.


Thanks,

Arvind Patil

Highlighted
Specialist
Specialist

Hi, Umashankar. To achieve this you can use Generic LOAD

GenTable: Generic Load NBR, Group, Count Resident InputTable;

ResultTable: LOAD Distinct NBR Resident InputTable;


FOR i = 0 to NoOfTables()

  TableList:

  LOAD TableName($(i)) as Tablename AUTOGENERATE 1

  WHERE WildMatch(TableName($(i)), 'GenTable.*');

NEXT i

FOR i = 1 to FieldValueCount('Tablename')

  LET vTable = FieldValue('Tablename', $(i));

  LEFT JOIN (ResultTable) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

NEXT i

DROP TABLES TableList, InputTable;

View solution in original post

Highlighted

Highlighted
Contributor
Contributor

Make use of the pivot. It'll be easier.

Highlighted
Master II
Master II

Hi,

Please check this.

Capture.JPG

Highlighted
Contributor III
Contributor III

Thanks for the response