Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everybody,
I am looking for a solution for 2 days and nothing helped me.
I'm used to SQL but i'm all new on Qlik.
My problem is :
I have one table with a "Date" column (DD/MM/YYYY) and i success to fill another collum "Season" with this condition :
Load
...
if ("Date" >= '01/07/2017' and "Date" <= '30/06/2018', '2017-2018', ' ') as Season;
...
But i would like to create more conditions because my dates start at 2001 and a season in my case is from 01/07/YYYY and goes to 30/06/YYYY+1.
The goal is to use dates to create seasons, but I'm not supposed to use more than one "if... as Season". Using 2 "as Season" create an error : "Field names must be unique within table".
Anyone has an idea ?
Best regards and sorry for my english..
(Je comprends aussi le Français)
This is the solution i found and it's almost the same as Vu_Nguyen:
[LOAD]
if (Month("Date_du_bon") > 6 ,Year("Date_du_bon") & '-' & (Year("Date_du_bon")+1), (Year("Date_du_bon")-1) & '-' & Year("Date_du_bon")) as Saison;
It use one condition and it's "lighter".
Thank you for your help !
Thank you for your answer:
I think your solution doesn't include the fact that a season is walking between two years.
for example: an order on 12/10/2017 and an order on 05/02/2018 must have the same season.
The only solution i found is to use this structure : if(date>='01/07/2017' and date<='30/06/2018', '2017-2018', if(Date>='01/07/2018' and date<='30/06/2019', '2018-2019', ' ')) ...
But as i said i have many years, and i would like to use something like a "switch .. case" structure
Hi Nuckpio,
The following piece of script should work (sample dates included):
Transactions:
LOAD
*,
If(TransDate >= MakeDate(Year(TransDate),7,1),
Text(Year(TransDate)) & '-' & Text(Year(TransDate)+1),
Text(Year(TransDate)-1) & '-' & Text(Year(TransDate))
) as Season;
LOAD
TransID,
Date(Date#(TransDate,'DD/MM/YYYY')) as TransDate;
LOAD * Inline [
TransID, TransDate
1, 05/05/2001
2, 19/07/2002
3, 27/09/2017
4, 15/03/2019
];
Resulting table is as follows:
This is the solution i found and it's almost the same as Vu_Nguyen:
[LOAD]
if (Month("Date_du_bon") > 6 ,Year("Date_du_bon") & '-' & (Year("Date_du_bon")+1), (Year("Date_du_bon")-1) & '-' & Year("Date_du_bon")) as Saison;
It use one condition and it's "lighter".
Thank you for your help !