Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
We conduct surveys at stores to count items. Some are mandatory (ie they have to be available) and others are not. So if at least one of the mandatory items is not available, then the result should be "No" , even if other items are available.
In my table below, the 1st 6 items are mandatory, but because "Survey Response" on four of them is "No", then for each line should be "No".
How do I create this expression?
The expression should be specific to a customer and survey date.
Kind regards
Nayan
Month | CustomerID | Customer | Sales Rep | Segment | Survey Code | Region | Category | SKU | Survey Date | UOM Code | Survey Response | Mandatory |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Rum | Captain Morgan | 23/01/2018 | BU | No | Yes |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Whisky | Bells | 23/01/2018 | BU | Yes | Yes |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Whisky | Black & White | 23/01/2018 | BU | No | Yes |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Whisky | Jw Black | 23/01/2018 | BU | No | Yes |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Whisky | Jw Red | 23/01/2018 | BU | No | Yes |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Spirits | Smirnoff 1818 | 23/01/2018 | BU | Yes | Yes |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Liqueurs | Gilbeys Apple | 23/01/2018 | BU | Yes | |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Liqueurs | Gilbeys Berry | 23/01/2018 | BU | No | |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Liqueurs | Gilbeys Orange | 23/01/2018 | BU | No | |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Spirits | Saluta | 23/01/2018 | BU | No |
Hi,
You will have to create a separate table which has the clients and date where it was mandatory but it has No.
Something like below.
Temp:
Load Distinct CustomerID&Date as Key
From XYZ;
where Mandatory = 'Yes' and [Survey Response] = 'No';
MainData:
Load *,If(exists(Key,CustomerID&Date),'No',[Survey Response]) as NewResponse
From XYZ;
Regards,
Kaushik Solanki
what do you want to calculate?
Please share expected output.
Hi Ali
I want to add a column that if one of the survey response is no for a mandatory item, then my answer should be no. See table below, the "Results" Column (last column). This is the result I'm looking for.
Hope it makes sense.
Kind regards
Nayan
Month | CustomerID | Customer | Sales Rep | Segment | Survey Code | Region | Category | SKU | Survey Date | UOM Code | Survey Response | Mandatory | Result |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Rum | Captain Morgan | 23/01/2018 | BU | No | Yes | No |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Whisky | Bells | 23/01/2018 | BU | Yes | Yes | No |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Whisky | Black & White | 23/01/2018 | BU | No | Yes | No |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Whisky | Jw Black | 23/01/2018 | BU | No | Yes | No |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Whisky | Jw Red | 23/01/2018 | BU | No | Yes | No |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Spirits | Smirnoff 1818 | 23/01/2018 | BU | Yes | Yes | No |
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Liqueurs | Gilbeys Apple | 23/01/2018 | BU | Yes | ||
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Liqueurs | Gilbeys Berry | 23/01/2018 | BU | No | ||
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Liqueurs | Gilbeys Orange | 23/01/2018 | BU | No | ||
Jan | A001 | ABC | John | Boys Bar | 201 | East Coast | Spirits | Saluta | 23/01/2018 | BU | No |
Hi,
You will have to create a separate table which has the clients and date where it was mandatory but it has No.
Something like below.
Temp:
Load Distinct CustomerID&Date as Key
From XYZ;
where Mandatory = 'Yes' and [Survey Response] = 'No';
MainData:
Load *,If(exists(Key,CustomerID&Date),'No',[Survey Response]) as NewResponse
From XYZ;
Regards,
Kaushik Solanki
Hi PM
I have just replied to Ali showing the expected output. It has to be specific to a customer and Survey Date. As a survey to a same customer is conducted many times in a month.
Kind regards
Nayan
Hi Kaushik
Thank you for your reply. Will try it out and let you know.
Kind regards
Nayan
you can load from your table where surveyResponse ='NO' and Mandatory = 'YES'
something like that:
new_table:
mapping
load Distinct CustomerID & '|' & SurveyDate as Key, 'NO' as Result
resident your_table
where SurveyResponse='No' and Mandatory='YES'
final_table:
load *, applyMap('new_table',CustomerID & '|' & SurveyDate,'YES') as Result
resident your_table;
drop table your_table