Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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;
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';
and do drop table TAb1 at the end of the script
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.
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';
try the above code