topic Re: Inline any date (i.e. '01-03-2011') and subtract 90 days in QlikView

Inline any date (i.e. '01-03-2011') and subtract 90 days

Tue, 29 Nov 2011 19:54:23 GMT

Hello all. I'm new to this still. I need to create a report inputing different dates (i.e. 01-03-2011) and be able to subtract 90 days from that date. I'm having problems determining what format QV will understand in order to do this. I have an inline input table for my date in that format, MM-DD-YYYY. Or, even if I hardcode the date in the code, how can I format the date in order to use it as the end date, then determine my start date, 90 days prior? Thanks.

Re: Inline any date (i.e. '01-03-2011') and subtract 90 days

johnw — Tue, 29 Nov 2011 20:10:21 GMT

SET DateFormat='MM-DD-YYYY';

LOAD *
,date(EndDate-90) as StartDate
INLINE [
EndDate
01-03-2011
];

Re: Inline any date (i.e. '01-03-2011') and subtract 90 days

Tue, 29 Nov 2011 20:22:19 GMT

That's great John. I like that format. How about if I want to create two variables. One with the StartDate of 90 days prior and one with the hardcoded date? I guess, I don't know how to reference that table date when I do my SQL selection criteria. A variable would work better. Thanks.

Re: Inline any date (i.e. '01-03-2011') and subtract 90 days

johnw — Tue, 29 Nov 2011 20:35:04 GMT

If you're hardcoding just two dates, and only want to use them in SQL, why create a table or variable at all? That's just straight up SQL. But if you need variables for some reason:

LET vEndDate = makedate(2011,1,3);

LET vStartDate = date(vEndDate-90);

But those are QlikView dates, not SQL dates, or however you store your dates. In our system for example, most dates are stored as an integer in format YYYYMMDD. So I'd want to format my variables that way:

LET vEndDate = date(makedate(2011,1,3),'YYYYMMDD');

LET vStartDate = date(vEndDate-90,'YYYYMMDD');

Something:

LOAD ...

;

SQL SELECT ...

FROM ...

WHERE MyDate BETWEEN $(vStartDate) AND $(vEndDate)

;

But that seems like a lot of complication for no reason when I could just write this instead:

Something:

LOAD ...

;

SQL SELECT ...

FROM ...

WHERE MyDate BETWEEN 20101005 AND 20110103

;

Re: Inline any date (i.e. '01-03-2011') and subtract 90 days

Tue, 29 Nov 2011 20:37:03 GMT

Let me clarify. Below is my code. If I can convert the hardcoded date into a variable, then I can subtract the 90 days. I guess what I need is how to convert a hardcoded date into a variable date. Thanks so much!

ZEDY4QV:
Load
Year(BUDAT) & '-' & Month(BUDAT) & '-' & trim(

[Bakery Desc]) as %BakeryKey,
*
where BUDAT >= '$(vWeekEnd90Date)' // Previous 90 Days from any given date
AND BUDAT < '2011-01-03'
;

Load

FROM

[$(vQVDPath)ZEDY4QV.qvd] (qvd);

Re: Inline any date (i.e. '01-03-2011') and subtract 90 days

Tue, 29 Nov 2011 20:38:52 GMT

Thank you so much, John. You should be teaching this!!!