not working

[soniasweety]

Hi @sunny_talwar 

loveisfail

Vegar

where condition is not working in script..  below i am using.

where Date#(Calendar_Month,'YYYYMM')<=date(addmonths(today()-2,-45),'YYYYMM')

---Calendar_month  -- No months its showing  as DB there is  values for this CalendarMonth.

when i  hardcode the field its working.   like below

where Calendar_month<='201709';           ----- this is working.   

what might be the issue with first where condition?

how can i modify the where condition expression?

thanks

[marcus_sommer · Accepted solution]

I think your issue is that you want to compare "normal" numbers against dates (it are also numbers but a bit different - and independent from their formatting) and in this way they don't match.

To get your showed numbers you could use the following logic:

year(date) * 100 + month(date)

Quite similar and in your case probaly more suitable would be to use a consecutive YearMonth counter which could be created with:

year(date) * 12 + month(date)

and then you could use a where-clause like:

where year(date) * 12 + month(date) <= (year(today()-2) * 12 + month(today()-2)) - 45

Even more useful would be to add the above created YearMonth period-fields as standard fields within all your qvd's. It simplifies such where-clauses and provides also the possibility to pre-load the required time-frame within an autogenerate load like:

PeriodDummy: load (year(today()-2) * 12 + month(today()-2)) + 1 - recno() as Period autogenerate 45;

and the using a where exists() to keep the qvd-loading optimized like:

where exists(Period);

- Marcus

[sunny_talwar · Accepted solution]

When you say the condition is not working in the script? Do you mean it brings back no result? Or does it bring different number of records? This might make help if the number of records are different

Where Date#(Calendar_Month, 'YYYYMM') <= MonthStart(Today()-2, -45);

Because when you entered 201709... it automatically assumed  beginning of the month, but when you use AddMonths, it take you back 45 months to the exact date instead of beginning of the month. You can use MonthStart instead of AddMonths

[soniasweety]

still no luck 😞 same calendar month showing no data