Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
if table is like
order-id start-date end-date calculate-days
1hbs71 10/12/2020 25/01/2021 46
1231fd 18/01/2021 19/03/2021 32
dff78n3 10/02/2021 nul() -
dff78n3 10/03/2020 nul() -
how can set variable for end-date if null so it can be any date then in front end I can choose date from drop down list? i.e front end I can choose any value for nul()?
is possible?
Hi, an easy way to solve this is assign a very long date when you find null end dates, like:
If(IsNull(end-date),MakeDate(2099), end-date) as end-date
Also you can use star https://help.qlik.com/es-ES/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegu..., but then end-dates can be really any date, also the ones before the start date.
Another way to do this is via set analysis. Adding a flag on the back end will make this a lot easier, something like if(isnull("end-date"),1,0) as "null-end-date" and then in your expression, you would use something like count({$+<"end-date"=,"null-end-date"={1}>} "order-id"). This is using the union in set analysis to join the current selection with the null end date flag of 1 while ignoring the end-date field selection.
I would be cautious with adding this sort of functionality because it may be unintuitive for the user and could create challenges if they wanted to de-select these orders without an end date
Please help me with star script
I need it to be any date then user can select date and from end table can do calculation
Hi this can be something like:
Star is *;
LOAD
order-id,
start-date,
If(IsNull(end-date), '*', end-date) as end-date,
calcualte-days
From/Resident...
I tested with a dummy script I had an each selection of Population also filters the row without population:
Star is *;
LOAD States, Gender, If(Len(Trim(Population))>1,Population, '*') as Population
INLINE [
States, Gender, Population
South Dakota, Male, 270597
Iowa, Male,
Delaware, Male, 452689
South Dakota, Female, 281598
Iowa, Female, 1490809
Delaware, Female, 482925
];