Todays Date and using that as a condition

[Ahmed_Turnaround]

Hi,

In QlikView, is there a way to create and set a variable to todays date and then use that variable as a condition.

I.e. we have prices that often change and we keep the history of prices, but we assign them effective and expiry dates. What I want to do, is show the price of an item if the effective date is greater than or equal to today and the expiry date is less than or equal to today. So we have todays price. 

Is this doable? 

[StarinieriG · Accepted solution]

Hi,

you could do it in this way:

LET vToday = Today();

1) If you want to keep old price values on data model, you could create a new field, which is not null when it is a price that is not expired

If(EFFECTIVE_DATE<='$(vToday)' and EXPIRE_DATE>='$(vToday)', PRICE) AS TODAY_PRICE

2) If you prefer to keep only today's price you could use: LOAD * Where EFFECTIVE_DATE<='$(vToday)' and EXPIRE_DATE>='$(vToday)';

[Ahmed_Turnaround]

Thanks for your reply. 

The date held in the QVD I'm using is as YYYY-MM-DD, will this make a difference? Or do I need to set vTODAY in the same format? How is this done? 

In my QVD, the file holds the item, the price and other variables of interest, how do I then select this when I import my QVD, such as the attached screenshot. I'm assuming this code here? LOAD * Where EFFECTIVE_DATE<='$(vToday)' and EXPIRE_DATE>='$(vToday)';

Does this make sense?

Thanks.

[StarinieriG]

You have to add it inside the editor.

LOAD *
FROM
price.qvd
(qvd)
Where EFFECTIVE_DATE<='$(vToday)' and EXPIRE_DATE>='$(vToday)' <------- here
;

[Ahmed_Turnaround]

Hi,

Thanks for your reply. Thank you for that, does it matter that the date formats are different? The QVD holds the format as YYYY-MM-DD, do I need to define that somehow in the LET vToday = Today(); section?

[StarinieriG]

Sorry, I had missed that part, there is no problem, you could use it without define the format

[Ahmed_Turnaround]

Hi,

Thank you for reply reply. I was testing out your code and when I ran the script,  I saw the following:

ItemPricing 0 lines fetched - there should be tens of thousands showing as fetched lines. 

None of the prices seem to be showing. I have - showing in my straight table in the application. 

The code is as follows:

// Insert 10P Prices for Items.
Directory;
LOAD [ITEM ID],
[PRICE CODE],
[CURRENCY EXCHANGE CODE],
[EFFECTIVE DATE],
[EXPIRY DATE],
[REC RETAIL PRICE],
[PRICE BREAK 01]
FROM
[E:\Qlikdata\IP1 Insight Pack 1.4\Data\TA.ItemPricing.qvd]
(qvd)
WHERE([PRICE CODE] = '10' And [CURRENCY EXCHANGE CODE] = 'P' AND [EFFECTIVE DATE]<='$(vToday)' AND [EXPIRY DATE] >='$(vToday)');

Have I done something wrong?

[StarinieriG]

Have you already added:

LET vToday = Today();

on the first line of your script?

[Ahmed_Turnaround]

Hi,

Yes, please see below:

LET vTODAY = TODAY();

Directory;
LOAD [ITEM ID],
[PRICE CODE],
[CURRENCY EXCHANGE CODE],
[EFFECTIVE DATE],
[EXPIRY DATE],
[REC RETAIL PRICE],
[PRICE BREAK 01]
FROM
[E:\Qlikdata\IP1 Insight Pack 1.4\Data\TA.ItemPricing.qvd]
(qvd)
WHERE([PRICE CODE] = '10' And [CURRENCY EXCHANGE CODE] = 'P' AND [EFFECTIVE DATE]<='$(vToday)' AND [EXPIRY DATE] >='$(vToday)');

[Ahmed_Turnaround]

Hi,

I've figured it out. It was the fact that Today was shown as Today in the WHERE command. Whereas at the top of the script, it's defined as TODAY. So the case being different caused it to not work.