Date variable selection

[Anonymous]

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?

[rubenmarin]

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.

[zwilson_borg]

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

[coolguy123]

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 

[rubenmarin]

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
];