topic How to create pivot table with dynamic number of columns from straight in script in App Development

How to create pivot table with dynamic number of columns from straight in script

Alyona — Fri, 09 Oct 2020 05:40:08 GMT

Hi, Qlik experts!

I need your help!

I have the following straight table - clients and their active contracts:

Client #	Contract
Client 1	Contract 111
Client 1	Contract 222
Client 2	Contract 333
Client 3	Contract 444
Client 3	Contract 555
Client 3	Contract 666

Based on it I need to create pivot table in the script - for each client I need to show all its active contracts in 1 row (each contract should be in separate column):

Client #	Contract #1	Contract #2	Contract #3
Client 1	Contract 111	Contract 222
Client 2	Contract 333
Client 3	Contract 444	Contract 555	Contract 666

Is it possible to create this pivot table with dynamically defining the number of columns (using loops or other techniques)?

Important nuance is that we don't know what is the maximum number of active contracts one client could have (in my example - it is 3 contracts, but in reality it could be today 5 contracts, tomorrow 3 and the day after tomorrow 20).

Please give me advise how to do this.

Thanks in advance,

Alyona

Re: How to create pivot table with dynamic number of columns from straight in script

marcus_sommer — Fri, 09 Oct 2020 06:46:31 GMT

This could be done with The-Generic-Load.

- Marcus

Re: How to create pivot table with dynamic number of columns from straight in script

Alyona — Fri, 09 Oct 2020 08:26:08 GMT

Hi, Marcus!

Thanks, but I am not sure that generic load could help me with this task.

Actually, I have found the solution:

//Calculate for each Client the count of active contracts:
[Clients_and_contracts]:
LOAD
[Client #],
COUNT([Contract ]) AS ActiveContractCount
RESIDENT InitialTable
GROUP BY [Client #]
;

//Add details by contracts
LEFT JOIN (Clients_and_contracts)
LOAD
[Client #],
[Contract ]
RESIDENT InitialTable
;

RENAME TABLE [Clients_and_contracts] TO [Clients_and_contracts_old];

Clients_and_contracts:
NoConcatenate
LOAD
*,
IF([Client #] <> Previous([Client #]), 1, PEEK('ContractNum')+1) AS ContractNum,
RowNo() AS RowNum
RESIDENT Clients_and_contracts_old
WHERE
ActiveContractCount > 1 //need only client which have more than 1 active contract
ORDER BY [Client #], [Contract] asc
;

DROP TABLE InitialTable, Clients_and_contracts_old;

//Calculate the max number of parallel active contracts
MaxActiveContractCount:
LOAD
Max(ActiveContractCount) as MaxActiveContractCount
RESIDENT Clients_and_contracts
;

LET vMaxActiveContractCount = PEEK('MaxActiveContractCount ');

PIVOT:
LOAD
DISTINCT [Client #],
PEEK('Contract',RowNum-1,'Clients_and_contracts') AS [Contract #1]
RESIDENT Clients_and_contracts
WHERE ActiveContractCount>=2 AND ContractNum = 1
;

FOR i = 2 TO $(vMaxActiveContractCount)
LEFT JOIN (TEST)
LOAD
DISTINCT [Client #],
PEEK('Contract,RowNum+$(i)-2,'Clients_and_contracts') AS 'Contract #$(i)'
RESIDENT Clients_and_contracts
WHERE ActiveContractCount>=$(i) and ContractNum = 1
;
NEXT i

DROP TABLE MaxActiveContractCount, Clients_and_contracts;

Re: How to create pivot table with dynamic number of columns from straight in script

sbash — Wed, 21 Sep 2022 18:11:23 GMT

where is the TEST table created?

LEFT JOIN (TEST)