Qlik Data modeling converting table in desired Table Format

[chandu441]

Hi all,

I am having one scenario which I need to convert from Table_A to Table_B format in Data modelling using Qlik Script.

Attached Excel sheet for reference.

Thanks In Advance

Satya

[rubenmarin · Accepted solution]

Hi @chandu441, using crosstable and set analysis for each expression you can create that table.

Crosstable:

CrossTable(Category, Data)
LOAD UniqueID, 
     Current_Bucket, 
     Proposed_Bucket, 
     Action_Bucket
FROM
[.\Copy of Crosstabledatamodel.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Then create a table with Category as dimension and a expression for each column, each one with the needed set analysis: Count({<Data={'CeX'}>} UniqueID)

Or a pivot table with Data as vertical dimension and just Count(UniqueID) as expression, but in this case you will need add some fixed table to sort vertical dimension, by default it will show as:

[JMAROUF]

Hi @chandu441 ;

try this: 

TABLE_A:
CrossTable(CATEGORIE, ITEM)
LOAD UniqueID,
Current_Bucket,
Proposed_Bucket,
Action_Bucket
FROM    [......\Crosstabledatamodel.xlsx]   (ooxml, embedded labels, table is Sheet1);

ITEMS:
LOAD DISTINCT ITEM AS ITEMVALUE RESIDENT TABLE_A;

SUM_TOTAL:
LOAD CATEGORIE, COUNT(DISTINCT UniqueID) AS SUM_TOTAL RESIDENT TABLE_A GROUP BY CATEGORIE;

TABLE_B_tmp:
LOAD
CATEGORIE
RESIDENT TABLE_A;

GENERIC_Table:

GENERIC LOAD CATEGORIE,ITEM,UniqueID RESIDENT TABLE_A;
DROP TABLE TABLE_A;

FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'GENERIC_Table.*');
NEXT i

FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (TABLE_B_tmp) LOAD * RESIDENT [$(vTable)];
DROP TABLE [$(vTable)];
NEXT i

DROP TABLE TableList;

FOR i = 1 to FieldValueCount('ITEMVALUE')
LET CULOMN=FieldValue('ITEMVALUE', $(i));
IF i = 1 THEN
TABLE_B:
LOAD CATEGORIE,
COUNT(DISTINCT $(CULOMN)) AS $(CULOMN)
RESIDENT TABLE_B_tmp GROUP BY CATEGORIE;
ELSE
LEFT JOIN(TABLE_B)
LOAD CATEGORIE,
COUNT(DISTINCT $(CULOMN)) AS $(CULOMN)
RESIDENT TABLE_B_tmp GROUP BY CATEGORIE;
END IF
NEXT i

DROP TABLE TABLE_B_tmp;
DROP TABLE ITEMS;

LEFT JOIN(TABLE_B)
LOAD
CATEGORIE,
SUM_TOTAL
RESIDENT SUM_TOTAL;

DROP TABLE SUM_TOTAL;

Best regards;

[chandu441]

H rubenmarin,

Thanks for your solution which it works well in front end. But I need to create the table in back end so as per my requirement I've created the script as below.

Table_A:
LOAD UniqueID,
Current_Bucket,
Proposed_Bucket,
Action_Bucket
FROM
[E:\Qlikview_Main\Source Documents\17_Admin\Crosstable_datamodel.xlsx] (ooxml, embedded labels, table is Table_A);

Table_B:
crosstable (Category, Bucket, 1) LOAD * Resident Table_A;
Drop table Table_A;

NoConcatenate
Table_A:
Load *
Where not Match(CeX, '',' ',null()) or len(CeX)<>'0'
;
Load
Count(UniqueID) as CeX,
Category
//, UniqueID
Resident Table_B
Where Bucket='CeX'
Group by Category
//, UniqueID
;

/* -------------------------------- */
Concatenate(Table_A)
Mainstream:
Load *
Where not Match(Mainstream, '',' ',null()) or len(Mainstream)<>'0' ;
Load
Count(UniqueID) as Mainstream,
Category
//, UniqueID
Resident Table_B
Where Bucket='Mainstream'
Group by Category
//, UniqueID
;

Concatenate(Table_A)
NUCMainstream:
Load *
Where not Match(NUCMainstream, '',' ',null()) or len(NUCMainstream)<>'0' ;
Load
Count(UniqueID) as NUCMainstream,
Category
//, UniqueID
Resident Table_B
Where Bucket='NUCMainstream'
Group by Category
//, UniqueID
;

Concatenate(Table_A)
Online:
Load *
Where not Match(Online, '',' ',null()) or len(Online)<>'0' ;
Load
Count(UniqueID) as Online,
Category
//, UniqueID
Resident Table_B
Where Bucket='Online'
Group by Category
//, UniqueID
;

Concatenate(Table_A)
NA:
Load *
Where not Match(NA, '',' ',null()) or len(NA)<>'0' ;
Load
Count(UniqueID) as NA,
Category
//, UniqueID
Resident Table_B
Where Bucket='NA'
Group by Category
//, UniqueID
;

Drop table Table_B;

NoConcatenate
Table_B:
Load
Category
//, Sum(Category) as Category
, Sum(CeX) as CeX
, Sum(Mainstream) as Mainstream
, Sum(NUCMainstream) as NUCMainstream
, Sum(Online) as Online
, Sum(NA) as NA
Resident Table_A
Group by Category;

Drop table Table_A;

Thanks 

chandu

[JMAROUF]

Hi @chandu441 

Have you tried my solution in back end? it will work even with dynamic fields ( without knowing values) .

regards.