Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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'.
@Vegar Please help
To better gasp what you are trying to do I would ask you to provide a data example with desired output.
However as I understand it you could probably come a long way simplifying stuff by using preceding load like in the pseudo code below.
LOAD
*,
IF(len([Direct Scrap] )=0 AND Len([Functional test])=0 AND LEN(Inspection)=0, true(), false()) as Store
;
LOAD
Applymap(...) as [Direct Scrap],
IF(...) as [Functional test],
applymap(...) as Inspection
FROM Data;
Hi,
I have attached sample data.
I want the following:-
I have one filter as direct scrap which I have already Implemented . Similarly I have fictional test condition which is working fine(attached sample data).
1. I want to have a field as a Store which will have the condition as direct scrap is blank and functional test is blank and inspection will be all those line items which are neither associated as functional test and direct scrap is to be also excluded(all these three conditions should meet ) and I should have a filter as store.
2. Once the above store condition is implemented then I need all the four filters(direct scrap,functional test,store and inspection) which are implemented separately should combined in a single column as a single filter.
like in below screenshot I have different filters for direct scrap, store , functional test and inspection I want that all three fields which are shown individually should be combined in a single column as functional test/Direct Scrap/Functional test/Inspection.
Please help @Vegar , @sunny_talwar
Hi,
I want after applying all apply map conditions for inspection field (as shown below). i want if the condition will meet then it should be replaced as 'Inpection'. In the (direct scrap/functional test /store/inspection) filter the inspection word should be present in the filter(circled in black colour) as seen below in the screenshot
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],'') as Inspection
Your all the applymaps in your Inspection will alway return blanks as they all look like this.
//The map table will return '' if you match the something value
Map_Something:
mapping
LOAD
[Something],
''
FROM
[Somehting.xlsx]
(ooxml, embedded labels, table is Something);
LOAD
ApplyMap('Map_Something',[Something],'') //Return the map value (that is blank '') if match, but also blank if there is no match (according to the third parameter '')
My guess is that you need to put 'Inspection' as a returning value in one of the two, if I understand your issue correctly then you need the applymaps to return 'Inspection'. Like this
Map_Something:
mapping
LOAD
[Something],
'Inspection'
FROM
[Somehting.xlsx]
(ooxml, embedded labels, table is Something);
Hi,
I tried doing the same what you suggested but I am getting the following output. I should only get values under Direct Scrap/functional Test/store/Inspection filter as Inspection,store,direct scrap,functional test instead of direct scrap inspection ,direct scrap store and so on.... (as seen in screenshot)
Hi
I also want in the apply map condition (used to check inspection filter) to check one more condition for apply map ie comments( available in EasyGTV data) so how to add that condition in the below code?
Comments field is is available in some other other table ie easyGTV data
Please help ? @Vegar