Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello ,
J'ai un cas à vous soumettre.
Data Type | Year | Month | Amount |
B15 | 2015 | 200 | |
B15 | 2015 | 150 | |
B15 | 2015 | 1100 | |
B15 | 2015 | 36 | |
B15 | 2015 | 60 | |
B15 | 2015 | 12 |
La colonne des mois est vide. Je veux integrer tous les mois de l'année. J'ai actuellement 6 lignes dans mon tableau.
Au final j'aimerais avoir un tableau de 6 * 12.
je vous remercie d'avance de votre aide.
source:
LOAD [Data Type],
Year,
// Month,
Amount
FROM
[https://community.qlik.com/thread/173887]
(html, codepage is 1252, embedded labels, table is @1);
// EDIT: test data
load [Data Type], Year-1 as Year, Amount Resident source;
tmp: load Distinct Year Resident source;
join (tmp) load Year, month(makedate(Year, IterNo())) as Month Resident tmp While IterNo() <= 12;
left join (source) load * Resident tmp;
DROP Table tmp;
source:
LOAD [Data Type],
Year,
// Month,
Amount
FROM
[https://community.qlik.com/thread/173887]
(html, codepage is 1252, embedded labels, table is @1);
// EDIT: test data
load [Data Type], Year-1 as Year, Amount Resident source;
tmp: load Distinct Year Resident source;
join (tmp) load Year, month(makedate(Year, IterNo())) as Month Resident tmp While IterNo() <= 12;
left join (source) load * Resident tmp;
DROP Table tmp;
Grand merci Massimo.
Mais pourquoi le Year-1 et non Year tout coup.
I added the line
load [Data Type], Year-1 as Year, Amount Resident source;
because I wanted to check the script with 2 year: after that line you have in the source table the 2015 records and the 2014 records with the same amount
but I forget to delete that line or to commenet the line before posting the script
thanks for that