How to create a new column on basis of other columns string

[akshay_chavan]

Hi All,

I want to create new Column which is based on one different fields

eg. 

Table:

NUMBER	Flag	Date 1	Date 2	Closed Date
Number 1	HUB	01-12-2020	11-01-2021	11-01-2021(Date 2)
Number 2	HUB	11-12-2020	12-02-2021	12-02-2021(Date 2)
Number 3	NON HUB	13-12-2020	03-01-2021	13-12-2020(Date 1)
Number 4	NON HUB	24-12-2020	14-01-2021	24-12-2020(Date 1)
Number 5	HUB	05-12-2020	05-02-2021	05-02-2021(Date 2)

In the above table I want to create column closed date on the basis of, 

if flag against Number Column is HUB then show date of Date 2 field

if flag against Number Column is NON HUB then show date of Date 1 field

How I can achieve this?

Thanks In advance!!!

#qlik sense 

Regards 

[Vegar · Accepted solution]

Try an pick match approach for this. Like below.

pick(match(Flag,'HUB','NON HUB'),[Date 2],[Date 1])

[akshay_chavan]

Hi @Vegar ,

Thanks for solution. dates are showing as required.

but In this I also want that if there  is no date in Date1 or Date2 then show the todays date in the closed date column.

how to achieve this?

regards

[Vegar]

You could combine it with an alt() or coalesce() like this.

alt(pick(match(Flag,'HUB','NON HUB'),[Date 2],[Date 1]), today())

[akshay_chavan]

@Vegar ,

today date display like this - 2/14/2022

but I want it into this format - 2020-02-14 09:45:40.0000000

I tried it with Now(), but not get expected result.

Regards