topic setting Date from another Date data-row in QlikView

setting Date from another Date data-row

Tue, 29 Nov 2011 10:07:59 GMT

I have a Calendar Table with calendar and Fiscal Dates. I need to have a Document OnLoad Trigger which sets a Default Date, a Defaul Accounting Period (Fisca Month) and Default Fiscal Year.For the Default Date I need to set the Calendar Date from a Load-Date minus 3 Days (only 1 Row), i try this but with no success:

=ONLY({<DATE.DAY_DT={$(DATE(LOAD_DATE.DAY_DT-3)) }>} DATE.DAY_DT)

And for setting the Fiscal Month (AP), I have just a vague idea:

=ONLY({<DATE.DAY_DT={$(DATE(LOAD_DATE.DAY_DT-3)) }>} DATE.AP)

Any ideas?

Re: setting Date from another Date data-row

Miguel_Angel_Baeyens — Tue, 29 Nov 2011 10:37:30 GMT

Hi,

Try changin the syntax as below

=ONLY({< DATE.DAY_DT = {'$(=DATE(LOAD_DATE.DAY_DT -3))'} >} DATE.DAY_DT)

Note the quoting and the "=" before the Date() function in the set modifier. Note as well that Only() does return null if there is actually more than one possible value for the expression within, and not one value. Min() or MinString() instead of Only() should do.

Hope that helps.

Miguel

setting Date from another Date data-row

Tue, 29 Nov 2011 11:03:34 GMT

Hello MiguelIt works, but only if I use it without minus 3 days. This probably because I have onbly one date-row in LOAD_DATE. What other way could i do to get the DATE.DAY_DT minus 3 days from the LOAD_DATE-DAY_DT?

setting Date from another Date data-row

Miguel_Angel_Baeyens — Tue, 29 Nov 2011 11:06:04 GMT

Hi,

You can use instead the function ReloadTime() that returns a complete timestamp with the last reload time and date.

Hope that helps.

Miguel

setting Date from another Date data-row

Tue, 29 Nov 2011 11:08:37 GMT

The Date is data from a ETL-Load and I need to have a date minus 3 Days of the LOAD_DATE.DAY_DT

setting Date from another Date data-row

Miguel_Angel_Baeyens — Tue, 29 Nov 2011 11:15:36 GMT

Hi,

I'm afraid I'm issing something since I don't have your model in front of me. You can save the highest value (or the only value) of LOAD_DATE.DAY_DT in a variable, then use it, or does it come from a different file? In this case you can save the value of LOAD_DATE.DAY_DT in a QVD file then LOAD in the file where you want to get that date minus 3.

Hope that makes some sense.

Miguel

setting Date from another Date data-row

swuehl — Tue, 29 Nov 2011 11:17:50 GMT

uweh,

is your LOAD_DATE.DAY_DT a time stamp? Then you probably need to apply an additional daystart() function to get rid of the time fraction part before using it for the set element for DATE.DAY_DT.

I assume your calendar shows the complete range of dates in question.

Regards,

Stefan

setting Date from another Date data-row

Tue, 29 Nov 2011 14:24:09 GMT

This would work perfekt:

=ONLY({< DATE.DAY_DT = {'$(=(LOAD_DATE.DAY_DT))'}>} DATE.DAY_DT )

B ut I have the date it not 3 days back

DAY_DT is of type DATE

setting Date from another Date data-row

swuehl — Tue, 29 Nov 2011 14:54:52 GMT

Since it's all about just dates, no confidential data involved, would it be possible that you post a small sample app here to the forum?

setting Date from another Date data-row

Miguel_Angel_Baeyens — Tue, 29 Nov 2011 15:11:40 GMT

Hi,

So assuming LOAD_DATE.DAY_DT is created in a different file (so when you load the other file, LOAD_DATE.DAY_DT is empty), why don't you save it into a QVD file that can be easily read in the latter file so you always have the value to substract 3? Or do you mean you only have in this file one possible date for DATE.DAY_DT? Can you please elaborate on your model and number of documents involved?

Regards.

Miguel