Join table based on minimum values

[jessica_webb]

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

[Anil_Babu_Samineni]

Can you please provide sample with Real data. And you are expected is as 3rd Table?

[jonathandienst]

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

[MindaugasBacius · Accepted solution]

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:

IntervalMatch ‒ QlikView

[paulferguson80]

Hi,

Hope this helps

here is a practical application using the tables you have above using interval match

Many Thanks

Paul

[jessica_webb]

Thank you so much - this works absolutely perfectly.

Really appreciate your help, and will read up on intervalmatch now.

Very best wishes,

Jess

[jessica_webb]

Thanks for all suggestions. The IntervalMatch function worked perfectly.

[jessica_webb]

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

[MindaugasBacius]

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:

[jessica_webb]

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