topic Re: Make data to null in QlikView

Make data to null

bharatkishore — Fri, 15 Feb 2019 13:22:08 GMT

HI All,

I have 2 tables and i have a derived contract flag and these columns from second table:

[Sales Document] as Sales.Document_Contract,
Item as Item_Contract,
Equipment,
Date(Start_DT_New,'MM/DD/YYYY') as Start_DT_New,
Date(End_DT_New,'MM/DD/YYYY') as End_DT_New,
MaterialEntered

Now i have written an expression to get contract flag i.e.

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

Now where ever the i get Outside Contract Period i want all the above mentioned columns to be null.

Can you please help me how to achieve it.

Thanks,

Bharat

Attaching qvw for reference.

Please let me know if you need anything more

Re: Make data to null

pradosh_thakur — Fri, 15 Feb 2019 13:32:00 GMT

something in the line of below, Anyhow if you are going to make the column null and not use it, why not just filter it out from the data model.

load if(Contract_Flag=''Outside Contract Period',null(),Item_Contract) as Item_Contract,
if(Contract_Flag=''Outside Contract Period',null(),Equipment) as Equipment,

...

;



load 

...



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

you table;

Re: Make data to null

anushree1 — Fri, 15 Feb 2019 13:32:28 GMT

Try adding the below code after the existing script:

NoConcatenate
Final:
LOAD
' ' as Sales.Document,
' ' as Sales.Document.Item,
' ' as Equipment,
' ' as Posting.Date,
'' as PH5.Name,
'' as Equipment_Start_Date,
'' as Revenue,
'' as Amount_Type_2,
'' as Serial_Number ......(similarly add all the columns you want to be NULL)
Resident Tab1
where Contract_Flag='Contract Period';
concatenate
load *
Resident Tab1
where Contract_Flag='Outside Contract Period';

Re: Make data to null

anushree1 — Fri, 15 Feb 2019 13:36:21 GMT

and do drop table TAb1 at the end of the script

Re: Make data to null

jaibau1993 — Fri, 15 Feb 2019 13:38:37 GMT

Hi Bharat!

I am not sure if I have undertood you but may be the attached file solves your issue.

Hope it helps! Bests,

Jaime.

Re: Make data to null

bharatkishore — Fri, 15 Feb 2019 13:40:51 GMT

Thanks Anushree , but in front end i am getting only one filter in Contract flag not two filters i.e. 'Contract Period' and Outside Contract Period. Can you please tell me how to get both

Re: Make data to null

bharatkishore — Fri, 15 Feb 2019 13:42:46 GMT

Hi Pradosh,
The contract flag condition i am after left join i cannot give this one before left join statement. Can you please tell me how can i do..

Re: Make data to null

anushree1 — Fri, 15 Feb 2019 13:46:02 GMT

Tab1:
load *,
if(Posting.Date>=Start_DT_New and Posting.Date<=End_DT_New,'Contract Period','Outside Contract Period' )as Contract_Flag
Resident Revenue;
DROP Table Revenue;
NoConcatenate
Final:
LOAD
' ' as Sales.Document,
' ' as Sales.Document.Item,
' ' as Equipment,
' ' as Posting.Date,
'' as PH5.Name,
'' as Equipment_Start_Date,
'' as Revenue,
'' as Amount_Type_2,
'' as Serial_Number ,(... all other columns similary that must have NULL Values)

'Contract Period' as Contract_Flag

Resident Tab1
where Contract_Flag='Contract Period';
concatenate
load *
Resident Tab1
where Contract_Flag='Outside Contract Period';

Re: Make data to null

anushree1 — Fri, 15 Feb 2019 13:46:30 GMT

try the above code

Re: Make data to null

bharatkishore — Fri, 15 Feb 2019 14:07:34 GMT

Sorry Anushree not working.

Re: Make data to null

anushree1 — Fri, 15 Feb 2019 14:24:50 GMT

i see it working when Contract Flag ->Outside Contract Period Selected the Table is blank

else it shows the relevant records.

Please check the code starting from Noconcatenate in the attached file.

Please let me know if you were looking for some other output

Re: Make data to null

anushree1 — Fri, 15 Feb 2019 14:24:52 GMT

i see it working when Contract Flag ->Outside Contract Period Selected the Table is blank

else it shows the relevant records.

Please check the code starting from Noconcatenate in the attached file.

Please let me know if you were looking for some other output

Re: Make data to null

bharatkishore — Fri, 15 Feb 2019 14:40:06 GMT

Thank you Jamie.. Worked perfectly... But only problem is for few columns data is matching and few other equipments data is not matching

Re: Make data to null

bharatkishore — Fri, 15 Feb 2019 14:44:56 GMT

Thank you Anushree for your time.. Jamie code worked.. thanks once again..