Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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;
Wahoooo it seems so easy when you read the solution
Many many thanks i spent 4h to try...
Nice day to everybody
Chris