Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
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 |
Please note under CellValue the Team Member1 is Name of the Team Member1
and Team Member2 is Name of the team member 2