Need help with Logic

[neelamsaroha157]

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.

[sunny_talwar]

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;

[neelamsaroha157]

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.

[sunny_talwar]

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.

[neelamsaroha157]

Yes, totally agree with you

[neelamsaroha157]

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..

[sunny_talwar]

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

[neelamsaroha157]

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

[sunny_talwar · Accepted solution]

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;

[neelamsaroha157]

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

Cheers!!!!!!!!