topic Re: Transforming data in App Development

Transforming data

QlikTonic — Tue, 12 Feb 2019 10:01:57 GMT

Hi,

Maybe someone could help me please ! I don't know how to solve my problem.

it should not be complicated but i don t know how to do.

i would like to transform :

Date	Pièce	Intitulé	Débit	Crédit	Montant	TVA
31.01.2018	Pièce 1	essence	6220	1020	76.66
31.01.2018	Pièce 2	invitation clients	6100	1020	43.20
31.01.2018	Pièce 3	Client 1	1020	3000	5 649.75	-344.63
31.01.2018	Pièce 4	Essai	6500		1 200.00
31.01.2018	Pièce 4	Essai		1020	1 200.00

in :

Date	Pièce	Intitulé	Compte	Montant	TVA
31.01.2018	Pièce 1	essence	6220	-76.66	0.00
31.01.2018	Pièce 1	essence	1020	76.66	0.00
31.01.2018	Pièce 2	invitation clients	6100	-43.2	0.00
31.01.2018	Pièce 2	invitation clients	1020	43.2	0.00
31.01.2018	Pièce 3	Client 1	1020	-5649.75
31.01.2018	Pièce 3	Client 1	3000	5649.75	-344.63
31.01.2018	Pièce 4	Essai	6500	-1200
31.01.2018	Pièce 4	Essai	1020	1200

Thanks a lot

Chris

Re: Transforming data

rbartley — Tue, 12 Feb 2019 11:12:56 GMT

Hi Chris,

Since you are effectively unpivoting the Debit and Credit columns, I think the Crosstable() load script function should work. Take a look at this post:

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/LoadData/work-with-cross-tables.htm

Re: Transforming data

Gysbert_Wassenaar — Tue, 12 Feb 2019 12:32:44 GMT

Data:
LOAD Date, Pièce, Intitulé, Débit AS Compte , -Montant as Montant
FROM ...source ....
WHERE Len(Trim(Débit))>0;

CONCATENATE (Data)

LOAD Date, Pièce, Intitulé, Crédit AS Compte , Montant, TVA
FROM ...source ....
WHERE Len(Trim(Crédit))>0;

Re: Transforming data

QlikTonic — Tue, 12 Feb 2019 11:56:04 GMT

Wahoooo it seems so easy when you read the solution

Many many thanks i spent 4h to try...

Nice day to everybody

Chris