Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Need help - How to Pivot rows as columns

Hi,

I need help to get the below table columns to rows as the answer table given below. I have tried with Generic Load however it's output duplicate rows in the result table.

I have reviews an article where column are numbered as 1,2,3,4 as a Column ID and have achieved what I am looking for. However  in my requirement columns cannot be numbered as sequence 1,2,3,4

It's hard to numbered the columns as the below table is inserted as a result of QlikView extension and that exertion object can have various no of columns based on it's usage in deferent applications. Application ID will identify the data entry App

Help would be appreciated

Thanks!

Paul

Source table

   

Seq NoColumn NameColumn Value Mod DateApplication ID
125Customer IDC12335/06/2018ProductEntry
126Product NameOrange5/06/2018ProductEntry
127Qty505/06/2018ProductEntry
128Add Date5/06/20185/06/2018ProductEntry
129Customer IDB11115/06/2018ProductEntry
130Product NameApple5/06/2018ProductEntry
131Qty505/06/2018ProductEntry
132Add Date5/06/20185/06/2018ProductEntry

 

Expected results

   

Customer IDProduct NameQtyAdd Date
C1233Orange505/06/2018
B1111Apple505/06/2018
3 Replies
el_aprendiz111
Specialist
Specialist

Hi Paul

TMP:
LOAD [Seq No],
[Column Name],
[Column Value],
[Mod Date],
[Application ID]

FROM  [..\..\descargas\ENERO.xlsx](ooxml, embedded labels, table is dt);



DATA:
Generic  Load [Application ID],[Column Name], [Column Value] Resident TMP;



CombinedGenericTable:

Load distinct [Application ID] Resident TMP;


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

LET vTable=TableName($(i));

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

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

DROP TABLE [$(vTable)];

ENDIF

NEXT i


DROP Table TMP;
EXIT SCRIPT;

Anonymous
Not applicable
Author

Hi Jairo

I am getting duplicate rows in the results

Code mapping I have done to match your code

TMP:
LOAD ID as  [Seq No],
ColumnId as [Column Name],
CellValue as [Column Value],
ModifiedDate as [Mod Date],
SourceTable as [Application ID]

Data sample I have used

   

IDColumnIdSourceTableSourceIdCellValueModifiedDate
47964ReviewDate1Team541446022/06/201822/06/2018
47963ReviewFrequency1Team54144604522/06/2018
47959TeamMember1Team5414460Team Member122/06/2018
47958CaseManager1Team5414460TRUE22/06/2018
47957ReviewDate1Team541446011/06/201822/06/2018
47956ReviewFrequency1Team541446018022/06/2018
47953TeamMember1Team5414460Team Memaber 222/06/2018

Anonymous
Not applicable
Author

Please note under CellValue the Team Member1 is Name of the Team Member1

and Team Member2 is Name of the team member 2