Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Expression

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

7 Replies
Highlighted
Partner
Partner

Re: Expression

what do you want to calculate?

I can walk on water when it freezes
Highlighted
Master III
Master III

Re: Expression

Please share expected output.

Highlighted
Creator
Creator

Re: Expression

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
Highlighted
MVP & Luminary
MVP & Luminary

Re: Expression

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Highlighted
Creator
Creator

Re: Expression

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

Highlighted
Creator
Creator

Re: Expression

Hi Kaushik

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

Kind regards

Nayan

Highlighted
Partner
Partner

Re: Expression

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