Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bharatkishore
Creator III
Creator III

Make data to null

HI All,

@sunny_talwar 

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

1 Solution

Accepted Solutions
jaibau1993
Partner
Partner

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.

View solution in original post

13 Replies
pradosh_thakur
Master II
Master II

 

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;
Learning never stops.
anushree1
Specialist II
Specialist II

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';

anushree1
Specialist II
Specialist II

and do drop table TAb1 at the end of the script

jaibau1993
Partner
Partner

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.

View solution in original post

bharatkishore
Creator III
Creator III
Author

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
bharatkishore
Creator III
Creator III
Author

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..
anushree1
Specialist II
Specialist II

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';

anushree1
Specialist II
Specialist II

try the above code

bharatkishore
Creator III
Creator III
Author

Sorry Anushree not working.