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;

[sakshikaul]

@Vegar  Please help

[Vegar]

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;

[sakshikaul]

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.

[sakshikaul]

Please help @Vegar , @sunny_talwar 

[Vegar]

Like this?

See attached qvw

[sakshikaul]

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

[Vegar]

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

[sakshikaul]

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)

[sakshikaul]

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