Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

sivareddye
New Contributor

Data transpose

hi

Please find the attached.

The first tab contains the raw data and I want the output results exactly as in the "output" tab.

articleid is the key here.One article id will have multiple rows."ColName" field should be there in the column and "ColValue" should be in the respective rows for each articleId.

I want to use your own creativity to come up with the dynamic logic which should accommodate n number of columns/rows for each articleid.i.e I don't want you to simply import as a cross table.

Can any one help me.

regards

reddy

3 Replies

Re: Data transpose

please create a shorter, clearer example and elaborate on your requirements.

thanks

regards

Marco

Re: Data transpose

Hi Siva,

I used the below steps for your file..

1. Load Your Excel file ,(Store it as a Qvd , So, you can use it future, instead of loading Excel)

2. Create the Generic Load for transforming The Generic Load

3. Join All your tables into single table..(Check the rwunderlich reply, in the above mentioned Document)

That's it..

/******* LOAD THE BELOW SCRIPT FROM THE EXCEL - STORE THE TABLE AS QVD FILE  ****/

T1:

LOAD AutoNumber(StructureID&AtricleID,RecNo()) as Key,

  RowID,

     ColID,

     StructureID,

     AtricleID,

     ColName,

     ColValue

FROM

[Task - 02022016.xlsx]

(ooxml, embedded labels, table is Data) Where isnum(RowID) and IsNum(ColID);

T2:

Generic LOAD AtricleID,ColName,ColValue Resident T1;

CombinedGenericTable:

Load distinct AtricleID Resident T1;

 

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'T2.*') THEN

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

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

DROP Table T1;

Check the Attachment..

sivareddye
New Contributor

Re: Data transpose

Thank you very much.

Community Browser