Make data to null

[bharatkishore]

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

[pradosh_thakur]

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;

[anushree1]

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]

and do drop table TAb1 at the end of the script

[jaibau1993 · Accepted solution]

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.

[bharatkishore]

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]

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]

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]

try the above code

[bharatkishore]

Sorry Anushree not working.