$ sign expansion for dates

[andreacossu1]

I have a simple table with 3 columns:

Date 1 containing dates

Date 2 containing different dates

ChosenDate containing either the value Date1 or Date 2.

For each record I would like to see a field called NewDate which is either Date1 or Date2 depending on the value in ChosenDate.  Currently I tried $(ChosenDate) as NewDate but no luck.  Any suggestions?

[rwunderlich]

Are you trying to do this in the script or in a chart?

[andreacossu1]

script

[Vegar]

Try this, I think it meets your need.

Pick(Match([ChosenDate],'Date1', 'Date2') ,[Date 1], [Date 2]) sa NewField

[rwunderlich]

I would think

[$(ChosenDate)] as NewDate

would do it. What error/problem are you getting when you use that in script?

-Rob

[andreacossu1]

Thanks for the suggestion - it doesn't work however.  The following error messages comes up:

The following error occurred:

Field '' not found

The error occurred here:

?

[Vegar]

Im not certain that a dollar expansion will work, but you could try to adjust @rwunderlich proposal like this

[$(=ChosenDate)] as NewDate

[rwunderlich]

Ok, I just reread the original post.  I see know we are talking about fields and varying per row.  Try this.

if(ChosenDate = 'Date1', Date1, Date2) as NewDate

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[andreacossu1]

Thanks both - unfortunately with the new dollar expansion I get the same error message.  The if statement or alternative the pick(match()) both work but aren't ideal as actually I have Date1, ..., Date 20 not just 2 dates and so I was hoping to avoid a large nested if statement or a large pick match statement....

[Vegar]

You could also loop through your fields.
For _type=1 to 20
Load
...
[Date $(_type)] as NewDate
FROM source
Where
ChosenDate = 'Date $(_type)'
Next

It might not be an better approach than the ones mentioned above, but you will get rid of the nested IF.