topic Re: Need help with Logic in QlikView

Need help with Logic

neelamsaroha157 — Tue, 27 Sep 2016 15:53:57 GMT

Hi,

I need help with a script logic.

Scenario is:

I have some IDs, Questions and Responses of the Question in a table.

But there are some of the Questions that need to skipped based on the answer of their previous Question.

I have attached a sample data and the logic to be incorporated and the output after logic.

I hope I am able to explain my case, if not let me know.

Thanks in advance.

Re: Need help with Logic

sunny_talwar — Tue, 27 Sep 2016 16:10:10 GMT

May be this:

Table:

LOAD ID,

QUESTION,

RESPONSE

FROM

TestData.xls

(biff, embedded labels, table is Sheet1$);

FinalTable:

LOAD *,

If(ID = Previous(Previous(ID)),

If(Previous(Previous(QUESTION)) = 'Ques-A' and Previous(Previous(RESPONSE)) = 'AHF', Null(), RESPONSE),

If(ID = Previous(ID),

If((Previous(QUESTION) = 'Ques-A' and Previous(RESPONSE) = 'AHF') or (Previous(QUESTION) = 'Ques-X' and Previous(RESPONSE) = 'No'), Null(), RESPONSE), RESPONSE)) as Outcome

Resident Table

Order By ID, QUESTION;

DROP Table Table;

Re: Need help with Logic

neelamsaroha157 — Tue, 27 Sep 2016 16:18:38 GMT

Hey Sunny,

Thanks for your time. So far it looks good with the sample data.

I'll apply it in real scenario and see If I am getting the expected resulted.

Again Thanks. I'll update you.

Re: Need help with Logic

sunny_talwar — Tue, 27 Sep 2016 16:21:00 GMT

Sounds good. Just make sure to fix the order by statement for QUESTION as I believe that it worked out well for the sample, but you might need to fix that for original Questions.

Re: Need help with Logic

neelamsaroha157 — Tue, 27 Sep 2016 16:25:56 GMT

Yes, totally agree with you

Re: Need help with Logic

neelamsaroha157 — Tue, 27 Sep 2016 20:45:23 GMT

Hey Sunny,

I found a few more combination in real data and I modified the sample data accordingly.

Everything else is working fine except the where Previous to Previous case exists.

Like in updated data ID=68, Ques-C should be null. but its not picking it up.

I have modified the existing condition a bit.

I did try to dig it but couldn't find where I am making mistake.

Thanks a ton..

Re: Need help with Logic

sunny_talwar — Tue, 27 Sep 2016 20:50:14 GMT

Is there a reason you moved the Previous(Previous( check after Previous( check?

Re: Need help with Logic

neelamsaroha157 — Tue, 27 Sep 2016 20:54:05 GMT

If I move that up then 1st condition doesn't work...

Re: Need help with Logic

sunny_talwar — Wed, 28 Sep 2016 02:49:39 GMT

May be try this out:

Table:

LOAD ID,

QUESTION,

RESPONSE

FROM

[TestData2.xls]

(biff, embedded labels, table is Sheet1$);

FinalTable:

LOAD ID,

QUESTION,

RESPONSE,

If(IsNull(Temp2) or IsNull(Temp), Null(), Temp) as Outcome;

LOAD *,

If(ID = Previous(Previous(ID)),

If(Previous(Previous(QUESTION)) = 'Ques-A' and Previous(Previous(RESPONSE)) = 'AHF', Null(), RESPONSE),

If(ID = Previous(ID),

If((Previous(QUESTION) = 'Ques-A' and Previous(RESPONSE) = 'AHF') or (Previous(QUESTION) = 'Ques-X' and Previous(RESPONSE) = 'No'), Null(), RESPONSE),

RESPONSE)) as Temp2,

If(ID = Previous(ID),

If((Previous(QUESTION) = 'Ques-A' and Previous(RESPONSE) = 'AHF') or (Previous(QUESTION) = 'Ques-X' and Previous(RESPONSE) = 'No'), Null(), RESPONSE), RESPONSE) as Temp

Resident Table

Order By ID, QUESTION;

DROP Table Table;

Re: Need help with Logic

neelamsaroha157 — Wed, 28 Sep 2016 15:41:44 GMT

Hey Sunny Thanks a lot....it worked great .

Cheers!!!!!!!!

Re: Need help with Logic

sunny_talwar — Wed, 28 Sep 2016 17:25:19 GMT

Awesome