Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2018-01-31 04:09 AM

Expression

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

             

MonthCustomerIDCustomerSales RepSegmentSurvey CodeRegionCategorySKUSurvey DateUOM CodeSurvey ResponseMandatory
JanA001ABCJohnBoys Bar201East CoastRumCaptain Morgan23/01/2018BUNoYes
JanA001ABCJohnBoys Bar201East CoastWhiskyBells23/01/2018BUYesYes
JanA001ABCJohnBoys Bar201East CoastWhiskyBlack & White23/01/2018BUNoYes
JanA001ABCJohnBoys Bar201East CoastWhiskyJw Black 23/01/2018BUNoYes
JanA001ABCJohnBoys Bar201East CoastWhiskyJw Red 23/01/2018BUNoYes
JanA001ABCJohnBoys Bar201East CoastSpiritsSmirnoff 1818 23/01/2018BUYesYes
JanA001ABCJohnBoys Bar201East CoastLiqueursGilbeys  Apple23/01/2018BUYes
JanA001ABCJohnBoys Bar201East CoastLiqueursGilbeys Berry 23/01/2018BUNo
JanA001ABCJohnBoys Bar201East CoastLiqueursGilbeys  Orange23/01/2018BUNo
JanA001ABCJohnBoys Bar201East CoastSpiritsSaluta23/01/2018BUNo
754 Views
0 Likes
1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP
‎2018-01-31 04:21 AM

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

686 Views
0 Likes
7 Replies
ali_hijazi
Partner - Master II
Partner - Master II
‎2018-01-31 04:12 AM

what do you want to calculate?

I can walk on water when it freezes
686 Views
0 Likes
prma7799
Master III
Master III
‎2018-01-31 04:19 AM

Please share expected output.

686 Views
0 Likes
Anonymous
Not applicable
‎2018-01-31 04:19 AM
Author

In response to ali_hijazi

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

              

MonthCustomerIDCustomerSales RepSegmentSurvey CodeRegionCategorySKUSurvey DateUOM CodeSurvey ResponseMandatoryResult
JanA001ABCJohnBoys Bar201East CoastRumCaptain Morgan23/01/2018BUNoYesNo
JanA001ABCJohnBoys Bar201East CoastWhiskyBells23/01/2018BUYesYesNo
JanA001ABCJohnBoys Bar201East CoastWhiskyBlack & White23/01/2018BUNoYesNo
JanA001ABCJohnBoys Bar201East CoastWhiskyJw Black 23/01/2018BUNoYesNo
JanA001ABCJohnBoys Bar201East CoastWhiskyJw Red 23/01/2018BUNoYesNo
JanA001ABCJohnBoys Bar201East CoastSpiritsSmirnoff 1818 23/01/2018BUYesYesNo
JanA001ABCJohnBoys Bar201East CoastLiqueursGilbeys  Apple23/01/2018BUYes
JanA001ABCJohnBoys Bar201East CoastLiqueursGilbeys Berry 23/01/2018BUNo
JanA001ABCJohnBoys Bar201East CoastLiqueursGilbeys  Orange23/01/2018BUNo
JanA001ABCJohnBoys Bar201East CoastSpiritsSaluta23/01/2018BUNo
686 Views
0 Likes
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP
‎2018-01-31 04:21 AM

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
687 Views
0 Likes
Anonymous
Not applicable
‎2018-01-31 04:22 AM
Author

In response to prma7799

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

686 Views
0 Likes
Anonymous
Not applicable
‎2018-01-31 04:23 AM
Author

In response to kaushiknsolanki

Hi Kaushik

Thank you for your reply.  Will try it out and let you know.

Kind regards

Nayan

686 Views
0 Likes
ali_hijazi
Partner - Master II
Partner - Master II
‎2018-01-31 04:29 AM

In response to Anonymous

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

I can walk on water when it freezes
686 Views