combining of two conditions in one column

[sakshikaul]

Hi all,

I have four conditions in a script as follows:-

a) ApplyMap('Scrap_Claim_Type',"Claim type [PAR]",'')                                                     As [Direct Scrap],

b) If([Damage Tag]= 1,ApplyMap('Functional_Service_ID',[Service No.],''))                As [Functional Test],

c)  ApplyMap('Central_Report_Claim_Type',[Claim type PAR BW])&
     & ApplyMap('Central_Report_Claim_Approved_BY',[Claim Approved By])
     & ApplyMap('Central_Report_Claim_Amount',[Cost excl. Tax])                                       as Inspection

d) This condition needs to be developed as excluding all above three  conditions      and rename it as Store  

So how to write a condition in script for this 4th point?

and Also, I want to combine all above statements in a single line using if condition.........

Following is the code given below and I want to combine all three conditions marked in blue color and also 4th condition (explained above) into single condition and rename that field as (Inspection/store/direct scrap/functional Test) 

Audi_Part:

Load

ApplyMap('Scrap_Claim_Type',"Claim type [PAR]",'')                                           As [Direct Scrap],

If([Damage Tag]= 1,ApplyMap('Functional_Service_ID',[Service No.],''))                    As [Functional Test],

Final_Audi_Part4:
load*,
AddMonths([Final Packing Slip Date BW], 1) as [Due Date @ WPRC BW],
ApplyMap('Central_Report_Claim_Type',[Claim type PAR BW])                   as Inspection

Resident Audi_Part;
Drop Table Audi_Part;

[Vegar]

You are getting duplicat Inspections like "InspectionInspection" because more than one applymap() are returning an "Inspection" value. You could problably work around this doing something like this:

IF(LEN(
ApplyMap('Central_Report_Claim_Type',[Claim type PAR BW],'')
&ApplyMap('Central_Report_Claim_NO',Claim_No_SN,'')
&ApplyMap('Central_Report_VIN',[VIN BW],'')
&ApplyMap('Central_Report_Mateial',Material,'')
& ApplyMap('Central_Report_Brand', Brand,'')
&ApplyMap('Central_Report_Dealer_Code',[Dealer Code],'')
& ApplyMap('Central_Report_Service_No', [Service No. BW],'')
& ApplyMap('Central_Report_Production_Date',[Production Date BW],'')
& ApplyMap('Central_Report_Mileage',[Mileage Bw],'')
& ApplyMap('Central_Report_Damage_Tag',[Damage Tag],'')
& ApplyMap('Central_Report_Damage_Type',[Damage: Type BW],'')
& ApplyMap('Central_Report_Sales_Model',[Vehicle: Sales Model BW],'')
& ApplyMap('Central_Report_Engine_Code',[Engine Code BW],'')
& ApplyMap('Central_Report_Gearbox',[Gearbox Code BW],'')
& ApplyMap('Central_Report_Claim_MFG_Code',[MFG Code BW],'')
& ApplyMap('Central_Report_Claim_SN',[SN BW],'')
& ApplyMap('Central_Report_Claim_Approved_BY',[Claim Approved By],'')
& ApplyMap('Central_Report_Claim_Amount',[Cost excl. Tax],'')
)>0, 'Inspection', null()) as Inspection

The "Functional TestInspection" and "FunctionalTestInspectionInspection" are the result of that both your logical tests are returning values. Both the Inspection and the Functional Test field. You need to handle this in some way, but firstly you need to figure which value should be the correct value for each of the abnormal rows.

[sakshikaul]

Hi Vegar,

This is working fine but I want in below screenshot when I select Scrap then the scrap inspection should not be included means there should be no association with inspection filter as well as blank of functional test (if the values in direct scrap are matched ie value is scrap then  those values should not be included in inspection and functional test filter )

@Vegar  I want to just handle this condition so can you please help me for the same ?

[Vegar · Accepted solution]

Try this.

IF([Direct Scrap] = 'Scrap',
IF(LEN(ApplyMap('Central_Report_Claim_Type',[Claim type PAR BW],'')
&ApplyMap('Central_Report_Claim_NO',Claim_No_SN,'')
&ApplyMap('Central_Report_VIN',[VIN BW],'')
&ApplyMap('Central_Report_Mateial',Material,'')
& ApplyMap('Central_Report_Brand', Brand,'')
&ApplyMap('Central_Report_Dealer_Code',[Dealer Code],'')
& ApplyMap('Central_Report_Service_No', [Service No. BW],'')
& ApplyMap('Central_Report_Production_Date',[Production Date BW],'')
& ApplyMap('Central_Report_Mileage',[Mileage Bw],'')
& ApplyMap('Central_Report_Damage_Tag',[Damage Tag],'')
& ApplyMap('Central_Report_Damage_Type',[Damage: Type BW],'')
& ApplyMap('Central_Report_Sales_Model',[Vehicle: Sales Model BW],'')
& ApplyMap('Central_Report_Engine_Code',[Engine Code BW],'')
& ApplyMap('Central_Report_Gearbox',[Gearbox Code BW],'')
& ApplyMap('Central_Report_Claim_MFG_Code',[MFG Code BW],'')
& ApplyMap('Central_Report_Claim_SN',[SN BW],'')
& ApplyMap('Central_Report_Claim_Approved_BY',[Claim Approved By],'')
& ApplyMap('Central_Report_Claim_Amount',[Cost excl. Tax],'')
& ApplyMap('Central_Report_LDC_Notification',[Comments :],'')
)>0, 'Inspection', null())
)
as Inspection

It will cause your logic only to run when you don't have defined the row as 'Scrap'.  

[sakshikaul]

I will try this and will let u know. Please give me sometime.