topic Re: Need help - How to Pivot rows as columns in QlikView

Need help - How to Pivot rows as columns

Anonymous — Thu, 21 Jun 2018 23:08:29 GMT

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 No	Column Name	Column Value	Mod Date	Application ID
125	Customer ID	C1233	5/06/2018	ProductEntry
126	Product Name	Orange	5/06/2018	ProductEntry
127	Qty	50	5/06/2018	ProductEntry
128	Add Date	5/06/2018	5/06/2018	ProductEntry
129	Customer ID	B1111	5/06/2018	ProductEntry
130	Product Name	Apple	5/06/2018	ProductEntry
131	Qty	50	5/06/2018	ProductEntry
132	Add Date	5/06/2018	5/06/2018	ProductEntry

Expected results

Customer ID	Product Name	Qty	Add Date
C1233	Orange	50	5/06/2018
B1111	Apple	50	5/06/2018

Re: Need help - How to Pivot rows as columns

el_aprendiz111 — Fri, 22 Jun 2018 02:26:19 GMT

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;

Re: Need help - How to Pivot rows as columns

Anonymous — Fri, 22 Jun 2018 03:02:28 GMT

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

ID	ColumnId	SourceTable	SourceId	CellValue	ModifiedDate
47964	ReviewDate1	Team	5414460	22/06/2018	22/06/2018
47963	ReviewFrequency1	Team	5414460	45	22/06/2018
47959	TeamMember1	Team	5414460	Team Member1	22/06/2018
47958	CaseManager1	Team	5414460	TRUE	22/06/2018
47957	ReviewDate1	Team	5414460	11/06/2018	22/06/2018
47956	ReviewFrequency1	Team	5414460	180	22/06/2018
47953	TeamMember1	Team	5414460	Team Memaber 2	22/06/2018

Re: Need help - How to Pivot rows as columns

Anonymous — Tue, 26 Jun 2018 00:13:26 GMT

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

and Team Member2 is Name of the team member 2