topic Re: not working in QlikView

not working

soniasweety — Sat, 16 Nov 2024 03:34:15 GMT

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

Re: not working

dplr-rn — Wed, 15 May 2019 19:28:49 GMT

try changing to below
Date(Calendar_Month,'YYYYMM')<=date(addmonths(today()-2,-45),'YYYYMM')
or
Date(Date#(Calendar_Month,'YYYYMM'),'YYYYMM')<=date(addmonths(today()-2,-45),'YYYYMM')

Re: not working

soniasweety — Thu, 16 May 2019 06:52:34 GMT

Hi Dilipranjith

both expressions not working 😞

Hi tresesco any help here?

Re: not working

tresB — Thu, 16 May 2019 07:17:09 GMT

Try like:

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

And also note,

Date(addmonths(today()-2,-45),'YYYYMM') = '201508' // Is this the value you want to compare with?

Re: not working

soniasweety — Thu, 16 May 2019 07:31:55 GMT

No.. its not like that.. i want to load the data beofre 45 months .. whatever comes under that condition it should in calender month field

Re: not working

tresB — Thu, 16 May 2019 07:51:12 GMT

Have you tried the expression I suggested? Is your Calendar_Month field in YYYYMM format?

Re: not working

soniasweety — Thu, 16 May 2019 08:33:21 GMT

Hi Tresesco yeah application is running in Qmc... once done i will update you.

and calendar month format is YYYYMM only.

Re: not working

soniasweety — Thu, 16 May 2019 09:34:19 GMT

Hi Tresesco

still its not working 😞

Re: not working

marcus_sommer — Thu, 16 May 2019 11:20:19 GMT

You mention a DB in your description. If you tries to apply Qlik functions within the sql-query it will fail. In this case you need to apply functions which your database and the used driver supports or if the queried dataset isn't too large you could apply the condition within a preceeding load, like:

load * where Condition;
sql select * from DB;

- Marcus

Re: not working

soniasweety — Thu, 16 May 2019 11:31:10 GMT

we have loaded full load to one qvd.. from that qvd we are dividing the multiple qvds.. there we are writing this where condtions .... so how to over come this?

Re: not working

marcus_sommer — Thu, 16 May 2019 12:50:41 GMT

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

Re: not working

sunny_talwar — Thu, 16 May 2019 13:04:48 GMT

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

Re: not working

soniasweety — Thu, 16 May 2019 14:32:53 GMT

still no luck 😞 same calendar month showing no data