topic Re: $ sign expansion for dates in App Development

$ sign expansion for dates

andreacossu1 — Thu, 23 May 2019 21:23:00 GMT

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?

Re: $ sign expansion for dates

rwunderlich — Thu, 23 May 2019 21:25:30 GMT

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

Re: $ sign expansion for dates

andreacossu1 — Thu, 23 May 2019 21:26:17 GMT

script

Re: $ sign expansion for dates

Vegar — Thu, 23 May 2019 21:42:52 GMT

Try this, I think it meets your need.

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

Re: $ sign expansion for dates

rwunderlich — Fri, 24 May 2019 04:10:06 GMT

I would think

[$(ChosenDate)] as NewDate

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

-Rob

Re: $ sign expansion for dates

andreacossu1 — Fri, 24 May 2019 13:39:47 GMT

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:

Re: $ sign expansion for dates

Vegar — Fri, 24 May 2019 14:00:30 GMT

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

[$(=ChosenDate)] as NewDate

Re: $ sign expansion for dates

rwunderlich — Fri, 24 May 2019 15:19:58 GMT

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

Re: $ sign expansion for dates

andreacossu1 — Fri, 24 May 2019 18:45:23 GMT

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....

Re: $ sign expansion for dates

Vegar — Fri, 24 May 2019 22:43:19 GMT

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.

Re: $ sign expansion for dates

rwunderlich — Fri, 24 May 2019 23:30:16 GMT

I think this will work:

Data:
LOAD *, RecNo() as Key INLINE [
ChosenDate, Date1, Date2, Date3
Date1, 100, 200, 300
Date2, 101, 201, 301
Date3, 103, 203, 303
Date2, 104, 204, 304
]
;

Choose:
LOAD
Key,
peek(peek('ChosenDate', RecNo()-1, 'Data'), RecNo()-1, 'Data') as NewDate
Resident Data
;

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

Re: $ sign expansion for dates

andreacossu1 — Mon, 27 May 2019 09:01:13 GMT

Thanks. Unfortunately I don't have RecNo in my table, which is a complex join of other tables.

Re: $ sign expansion for dates

rwunderlich — Mon, 27 May 2019 13:54:57 GMT

RecNo() is not a field, it's a counter function. You have it.

-Rob