Using an override value

[#1]

All,

I have a scenario that I am struggling to find a solution for.

An upstream SQL data base stores the results of a questionairre.

The results of questions are stored against systems and these are identified by unique systemID's.

It is possible for a particular system to complete a questionnaire but later it might be decided that it should inherit the results from another system. So an override field is then populated.

I need to get QlikView to display the results from the primary system against the downstream system that is inheriting these question results.

Attached is a very simple model that tries to show what we are trying to accomplish. In the live system there are around 100 questions and thousands of systems.

I tried to use a mapping table but this did not produce the required result set. It appeared to take the result of the first answer only from the primary system and then use this same response for the remaining question responses on the system that was inheriting.

Tom

P.S. We are using v9 SR3 7440

[johnw · Accepted solution]

If I understood what you're after:

LEFT JOIN (DATA01)
LOAD
SystemId as OverrideSystemId
,Question
,Result as OverrideResult
RESIDENT DATA01
;

expression = if(OverrideSystemID,OverrideResult,Result)

Edit: Actually, I'd just do THAT as another script step too:

LEFT JOIN (DATA01)
LOAD *
,if(OverrideSystemID,OverrideResult,Result) as FinalResult
RESIDENT DATA01
;

[#3]

John,

Brilliant. Thanks for the solution which took a few days to shoe-horn into our production model which is pretty large. This is now working perfectly. We had a bit a fun with the charts because if the 'OverrideSystemID' condition that needs to be tested but resolved this with a combination of Aggr and Avg functions.

Tom.