topic Re: Restricting data loaded using where clause in QlikView

Restricting data loaded using where clause

nevilledhamsiri — Wed, 25 Nov 2020 16:16:04 GMT

Hi Friends

I need to stop records where the CR Month = to PF month from being loaded. How this is achieved?. If I do not create two fields such as CR Month & PF Month, from the same data fields such as period from & credit note month,how this done with the where clause. Also I wish to know, the formula to use at the front end too.

Thanks a lot in advance

Neville

Re: Restricting data loaded using where clause

swuehl — Sun, 30 Sep 2018 10:08:54 GMT

Try using the Month() function in your WHERE clause

Where Month(CREDIT_NOTE_DATE) <> Month(PERIOD_FROM) ;

Re: Restricting data loaded using where clause

nevilledhamsiri — Sun, 30 Sep 2018 10:32:20 GMT

Thank you very much Sir, It is fine. Kindly note that if there is a record such as below where Month is same but Year is different (2017 vs 2018) in such cases still I need to show such records as long as Year if different. Please see how this condition too be included in the where clause

Neville

CREDIT_NOTE_NO	CREDIT_NOTE_DATE	PERIOD_FROM	PERIOD_TO	PREMIUM
RA0018C0010000	18-Jan-18	6-Jan-17	5-Mar-17	6797.5

Re: Restricting data loaded using where clause

nevilledhamsiri — Sun, 30 Sep 2018 10:51:04 GMT

Actually the above record rather looks like below!

CREDIT_NOTE_NO

CREDIT_NOTE_DATE

PERIOD_FROM

PERIOD_TO

PREMIUM

RA0018C0010000

18-Jan-18

6-Jan-17

5-Jan-17

6797.5

Re: Restricting data loaded using where clause

swuehl — Sun, 30 Sep 2018 11:08:39 GMT

Just use a function that returns the correct granularity, e.g. Monthname()

Where Monthname(CREDIT_NOTE_DATE) <> Monthname(PERIOD_FROM) ;

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/monthname.htm

And take care to get your dates right:

Get the Dates Right

Why don’t my dates work?

Re: Restricting data loaded using where clause

nevilledhamsiri — Sun, 30 Sep 2018 13:45:30 GMT

Thank you very much sir,

This is done & achieved