topic Re: Remove columns data in QlikView

Remove columns data

bharatkishore — Fri, 15 Feb 2019 06:32:51 GMT

HI All,

I have written the below condition to get flag in script:

if(Equipment=Equipment_Sales_LookUp

and Posting.Date>=Start_DT_New and Posting.Date<=End_DT_New,'Contract Period','Outside Contract Period' )as Contract_Flag

Now the problem is when i have data as Outside Contract Period i want to get the few fields data as null. i don't want to see any records . Can you please suggest me on this.

This should happen in script and all the fields are in 1 table.

Thanks,

Bharat

Re: Remove columns data

PrashantSangle — Fri, 15 Feb 2019 06:40:40 GMT

can you explain with some sample data??

Regards,
Prashant Sangle

Re: Remove columns data

bharatkishore — Fri, 15 Feb 2019 06:48:02 GMT

HI Prashant ,

In the above image i want Start date , End date , Salesdocument and Item columns as null because here the below condition is not matching

Posting.Date>=Start_DT_New and Posting.Date<=End_DT_New,'Contract Period','Outside Contract Period' )

where ever my posting date is <start end i want Start date , End date , Salesdocument and Item fields to be null.

These columns i bought in through mapping.

Please let me know if you need anything more.

Re: Remove columns data

PrashantSangle — Fri, 15 Feb 2019 06:58:24 GMT

Is your posting date, start date and end date in proper date format??? debug your flag expression
try to use num()
try below.

if(num(Posting.Date)>=num(Start_DT_New) and num(Posting.Date)<=num(End_DT_New),'Contract Period','Outside Contract Period' )

Regards,
Prashant Sangle

Re: Remove columns data

bharatkishore — Fri, 15 Feb 2019 07:10:56 GMT

I have changed while mapping them it-self as in the format of Posting date:
The below is the example:
Date(Date#(Start_DT_New,'YYYY/MM/DD'),'MM/DD/YYYY') as Start_DT_New
Date(Date#(End_DT_New,'YYYY/MM/DD'),'MM/DD/YYYY') as End_DT_New
will try with your one and let you know

Re: Remove columns data

bharatkishore — Fri, 15 Feb 2019 07:22:21 GMT

I tried with your expression but the result is same..

Re: Remove columns data

PrashantSangle — Fri, 15 Feb 2019 08:21:10 GMT

if(posting.date>start_date_new,'New','Old') as Flag1
if(posting.date<end_date_new,'New','Old') as Flag2

check this flag is evaluating properly or not

Regards,
Prashant Sangle

Re: Remove columns data

bharatkishore — Fri, 15 Feb 2019 08:52:04 GMT

I tried with your conditions and the flag is evaluating properly.

Below image for reference:

Please let me know if you need anything more.

Re: Remove columns data

PrashantSangle — Fri, 15 Feb 2019 09:20:23 GMT

Now combine both expression and try
if(posting.date>start_date_new and posting.date<end_date_new,'New','Old') as Flag1

Regards,
Prashant Sangle

Re: Remove columns data

bharatkishore — Fri, 15 Feb 2019 09:28:31 GMT

Sure but that is the same expression i have written earlier:

if(Equipment=Equipment_Sales_LookUp

and num(Posting.Date)>=num(Start_DT_New) and num(Posting.Date)<=num(End_DT_New),'Contract Period','Outside Contract Period' )as Contract_Flag,

Please let me know if i need to change any where

Re: Remove columns data

PrashantSangle — Fri, 15 Feb 2019 09:52:49 GMT

for time being I am debugging your expression only so remove 1st condition from your exisiting expression
if(num(Posting.Date)>=num(Start_DT_New) and num(Posting.Date)<=num(End_DT_New),'Contract Period','Outside Contract Period' )as Contract_Flag,

Regards,
Prashant Sangle

Re: Remove columns data

bharatkishore — Fri, 15 Feb 2019 11:20:15 GMT

PFA imabe.. this is the result i got after your condition. please let me know if you need anything more..

Re: Remove columns data

PrashantSangle — Mon, 18 Feb 2019 04:25:03 GMT

Now add one more expression which will test your 1st condition
if(Equipment=Equipment_Sales_LookUp,'True','False') as Flag

Regards,
Prashant Sangle