Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Apologies that I'm not able to provide any sample script, but I have no idea where to begin with this and can't find anything similar in the forums.
I would like to do the following in the script, as I believe it is more effective...
I have a very large table called TABLE_INFO with the following key fields (there are many other fields in addition to these):
Qual_pred | Is_A | Is_B | Is_C | Prior_AB | Prior_C |
---|---|---|---|---|---|
Spanish | 1 | 28 | 34 | ||
Spanish | 1 | 33 | 38 | ||
Spanish | 1 | 20 | 27 | ||
English | 1 | 43 | 54 | ||
English | 1 | 29 | 27 |
I then have another table (TABLE_NEW) which I would like to join to TABLE_INFO, with the following fields:
Qual_pred | Min | Max | Outcome |
---|---|---|---|
Spanish | 16 | 20 | 33 |
Spanish | 21 | 25 | 37 |
Spanish | 26 | 30 | 40 |
Spanish | 31 | 35 | 42 |
English | 18 | 21 | 28 |
English | 22 | 25 | 36 |
English | 26 | 29 | 38 |
I need to do the following for every row in TABLE_INFO.
1) Look at the 'Qual_pred'.
2) If it is 'Is_A' or 'Is_B', use value 'Prior_AB' / If it is 'Is_C', use value 'Prior_C'
3) Look in TABLE_NEW and find matching 'Qual_pred'
4) If value 'Prior_AB' or 'Prior_C' (depending on step 2), falls between 'Min' and 'Max', return 'Outcome' value to TABLE_INFO
I would hope to end up with the following:
Qual_pred | Is_A | Is_B | Is_C | Prior_AB | Prior_C | Outcome |
---|---|---|---|---|---|---|
Spanish | 1 | 28 | 34 | 40 | ||
Spanish | 1 | 33 | 38 | 42 | ||
Spanish | 1 | 20 | 27 | 33 | ||
English | 1 | 43 | 23 | 36 | ||
French | 1 | 17 | 29 |
Any help appreciated - even just a hint as to what direction I should go in!
Many thanks in advance,
Jess
There is the script:
Log:
LOAD *,
If(Is_C = 1, Prior_C, Prior_AB) as Data
FROM
[https://community.qlik.com/thread/232447]
(html, codepage is 1257, embedded labels, table is @1);
Info:
LOAD Qual_pred,
Min,
Max,
Outcome
FROM
[https://community.qlik.com/thread/232447]
(html, codepage is 1257, embedded labels, table is @2);
Inner join
IntervalMatch (Data, Qual_pred) LOAD Min, Max, Qual_pred Resident Info;
Results look like this:
The app is attached.
Also, please check the Help information - it analysis practicly the same issue as your's:
Can you please provide sample with Real data. And you are expected is as 3rd Table?
This looks like an interval match.
If you supply a sample qvw or at least a spreadsheet with some sample data, together with your expected results, you will get more specific and detailed help.
Preparing examples for Upload - Reduction and Data Scrambling
There is the script:
Log:
LOAD *,
If(Is_C = 1, Prior_C, Prior_AB) as Data
FROM
[https://community.qlik.com/thread/232447]
(html, codepage is 1257, embedded labels, table is @1);
Info:
LOAD Qual_pred,
Min,
Max,
Outcome
FROM
[https://community.qlik.com/thread/232447]
(html, codepage is 1257, embedded labels, table is @2);
Inner join
IntervalMatch (Data, Qual_pred) LOAD Min, Max, Qual_pred Resident Info;
Results look like this:
The app is attached.
Also, please check the Help information - it analysis practicly the same issue as your's:
Hi,
Hope this helps
here is a practical application using the tables you have above using interval match
Many Thanks
Paul
Thank you so much - this works absolutely perfectly.
Really appreciate your help, and will read up on intervalmatch now.
Very best wishes,
Jess
Thanks for all suggestions. The IntervalMatch function worked perfectly.
Hi Mindaugas,
Would you be able to clarify one last thing for me?
Later calculations in my script use various fields from TABLE_INFO, along with 'Outcome' from the new 'INFO'.
Is there any way to join the 'Outcome' field to TABLE_INFO, so that it is recognised in my calculations?
I tried joining 'INFO' to 'TABLE_INFO', but that just gave me lots of duplicate rows, with incorrect 'Outcome' field.
Thanks,
Jess
Log:
LOAD *,
If(Is_C = 1, Prior_C, Prior_AB) as Data
FROM
[https://community.qlik.com/thread/232447]
(html, codepage is 1257, embedded labels, table is @1);
Info:
LOAD Qual_pred,
Min,
Max,
Outcome
FROM
[https://community.qlik.com/thread/232447]
(html, codepage is 1257, embedded labels, table is @2);
inner join
IntervalMatch (Data, Qual_pred) LOAD Min, Max, Qual_pred Resident Info;
Left Join
Load *
Resident Log;
drop Table Log;
Try the above.
Result:
Unfortunately that's not working either.
I adjusted your script slightly for several reasons, to below:
RIGHT JOIN (TABLE_INFO)
LOAD *,
If(Is_C = 1, PRIOR_C,IF(Is_A = 1, PRIOR_AB, IF(Is_B= 1, PRIOR_AB))) as DATA
Resident TABLE_INFO;
INFO:
LOAD Qual_pred,
Min,
Max,
Outcome
FROM
(ooxml, embedded labels, table is Outcome);
Inner join
IntervalMatch (DATA, Qual_pred) LOAD Mini, Max, Qual_pred Resident INFO;
And was left with a synthetic table, and couldn't do calculated field with 'Outcome'.
I've now amended my script to be like yours
LOG:
LOAD *,
If(Is_C = 1, PRIOR_C,IF(Is_A = 1, PRIOR_AB, IF(Is_B= 1, PRIOR_AB))) as DATA
Resident TABLE_INFO;
INFO:
LOAD Qual_pred,
Min,
Max,
Outcome
FROM
(ooxml, embedded labels, table is Outcome);
Inner join
IntervalMatch (DATA, Qual_pred) LOAD Mini, Max, Qual_pred Resident INFO;
Left Join
Load *
Resident LOG;
drop Table LOG;
But still have a synthetic table (plus INFO and TABLE_INFO, with all three tables showing all available values.
Ideally, I only want 'TABLE_INFO' with every value in it.
Any ideas?
Thanks,
Jess