topic Re: Expression in QlikView

Expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Expression

ali_hijazi — Wed, 31 Jan 2018 09:12:56 GMT

what do you want to calculate?

Re: Expression

prma7799 — Wed, 31 Jan 2018 09:19:41 GMT

Please share expected output.

Re: Expression

Anonymous — Wed, 31 Jan 2018 09:19:56 GMT

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

Re: Expression

kaushiknsolanki — Wed, 31 Jan 2018 09:21:55 GMT

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

Re: Expression

Anonymous — Wed, 31 Jan 2018 09:22:00 GMT

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

Re: Expression

Anonymous — Wed, 31 Jan 2018 09:23:21 GMT

Hi Kaushik

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

Kind regards

Nayan

Re: Expression

ali_hijazi — Wed, 31 Jan 2018 09:29:40 GMT

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