Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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.
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.
Yes, totally agree with you
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..
Is there a reason you moved the Previous(Previous( check after Previous( check?
If I move that up then 1st condition doesn't work...
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;
Hey Sunny Thanks a lot....it worked great .
Cheers!!!!!!!!