Data transpose

[Anonymous]

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

[MarcoWedel]

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

thanks

regards

Marco

[settu_periasamy]

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..

[Anonymous]

Thank you very much.