Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Team,
How to create multiple cross table for below table.
EMPID | NAME | Q1 | Q2 | C1 | C2 | C3 | D1 | D2 |
1216 | GIRI | 10 | 5 | 8 | 8 | 7 | 7 | 9 |
1220 | SREE | 15 | 8 | 9 | 3 | 8 | 5 | 5 |
1219 | HARI | 4 | 6 | 6 | 2 | 9 | 3 | 1 |
Output:
EMPID | NAME | Q | C | D | QDATA | CDATA | DDATA |
1216 | GIRI | Q1 | C1 | D1 | 10 | 8 | 7 |
1216 | GIRI | Q2 | C2 | D2 | 5 | 8 | 9 |
1216 | GIRI | C3 | 7 | ||||
1220 | SREE | Q1 | C1 | D1 | 15 | 9 | 5 |
1220 | SREE | Q2 | C2 | D2 | 8 | 3 | 5 |
1220 | SREE | C3 | 8 | ||||
1219 | HARI | Q1 | C1 | D1 | 4 | 6 | 3 |
1219 | HARI | Q2 | C2 | D2 | 6 | 2 | 1 |
1219 | HARI | C3 | 9 |
Thanks,
Gireesh
Like this (replace Inline with actual data source):
SourceData: LOAD * INline [ MPID NAME Q1 Q2 C1 C2 C3 D1 D2 1216 GIRI 10 5 8 8 7 7 9 1220 SREE 15 8 9 3 8 5 5 1219 HARI 4 6 6 2 9 3 1 ] (delimiter is '\t'); T1: CrossTable(T_Col, T_Val, 2) LOAD * Resident SourceData; T2: LOAD MPID, NAME, T_Col as [Q], T_Val as QDATA, PurgeChar(T_Col, 'QCD') as TID Resident T1 Where T_Col Like 'Q*' Order By MPID, [T_Col]; Join (T2) LOAD MPID, NAME, T_Col as [C], T_Val as CDATA, PurgeChar(T_Col, 'QCD') as TID Resident T1 Where T_Col Like 'C*' Order By MPID, [T_Col]; Join (T2) LOAD MPID, NAME, T_Col as [D], T_Val as DDATA, PurgeChar(T_Col, 'QCD') as TID Resident T1 Where T_Col Like 'D*' Order By MPID, [T_Col]; DROP Table SourceData, T1;
Like this (replace Inline with actual data source):
SourceData: LOAD * INline [ MPID NAME Q1 Q2 C1 C2 C3 D1 D2 1216 GIRI 10 5 8 8 7 7 9 1220 SREE 15 8 9 3 8 5 5 1219 HARI 4 6 6 2 9 3 1 ] (delimiter is '\t'); T1: CrossTable(T_Col, T_Val, 2) LOAD * Resident SourceData; T2: LOAD MPID, NAME, T_Col as [Q], T_Val as QDATA, PurgeChar(T_Col, 'QCD') as TID Resident T1 Where T_Col Like 'Q*' Order By MPID, [T_Col]; Join (T2) LOAD MPID, NAME, T_Col as [C], T_Val as CDATA, PurgeChar(T_Col, 'QCD') as TID Resident T1 Where T_Col Like 'C*' Order By MPID, [T_Col]; Join (T2) LOAD MPID, NAME, T_Col as [D], T_Val as DDATA, PurgeChar(T_Col, 'QCD') as TID Resident T1 Where T_Col Like 'D*' Order By MPID, [T_Col]; DROP Table SourceData, T1;
Another way to do this
Table: CrossTable (LABEL, VALUE, 2) LOAD * INLINE [ EMPID, NAME, Q1, Q2, C1, C2, C3, D1, D2 1216, GIRI, 10, 5, 8, 8, 7, 7, 9 1220, SREE, 15, 8, 9, 3, 8, 5, 5 1219, HARI, 4, 6, 6, 2, 9, 3, 1 ]; TempTable: LOAD EMPID, NAME, LABEL, Left(LABEL, 1) as LABEL1, Right(LABEL, 1) as LABEL2, VALUE Resident Table; DROP Table Table; FinalTable: LOAD DISTINCT EMPID, NAME, LABEL2 Resident TempTable; FOR i = 1 to FieldValueCount('LABEL1') LET vLabel1 = FieldValue('LABEL1', $(i)); Left Join (FinalTable) LOAD DISTINCT EMPID, NAME, LABEL2, LABEL as [$(vLabel1)], VALUE as [$(vLabel1)DATA] Resident TempTable Where LABEL1 = '$(vLabel1)'; NEXT DROP Table TempTable;