topic Re: Join table based on minimum values in QlikView

Join table based on minimum values

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Join table based on minimum values

Anil_Babu_Samineni — Tue, 13 Sep 2016 08:58:42 GMT

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

Re: Join table based on minimum values

jonathandienst — Tue, 13 Sep 2016 09:09:43 GMT

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

Re: Join table based on minimum values

MindaugasBacius — Tue, 13 Sep 2016 09:10:44 GMT

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

Re: Join table based on minimum values

paulferguson80 — Tue, 13 Sep 2016 09:35:25 GMT

Hi,

Hope this helps

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

Many Thanks

Paul

Re: Join table based on minimum values

jessica_webb — Tue, 13 Sep 2016 09:42:35 GMT

Thank you so much - this works absolutely perfectly.

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

Very best wishes,

Jess

Re: Join table based on minimum values

jessica_webb — Tue, 13 Sep 2016 09:43:12 GMT

Thanks for all suggestions. The IntervalMatch function worked perfectly.

Re: Join table based on minimum values

jessica_webb — Tue, 13 Sep 2016 10:17:40 GMT

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

Re: Join table based on minimum values

MindaugasBacius — Tue, 13 Sep 2016 10:50:54 GMT

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:

Re: Join table based on minimum values

jessica_webb — Tue, 13 Sep 2016 11:10:51 GMT

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

Re: Join table based on minimum values

jessica_webb — Wed, 14 Sep 2016 09:21:59 GMT

Paul, that helps enormously - thank you so much!

I now have a single table of data, with the Outcomes matched in correctly.

For some reason I'm still getting duplicates in my worksheet table, showing nulls for outcome, e.g:

Qual_pred	Is_A	Is_C	Prior_AB	Prior_C	Outcome
Spanish	1		28	34	40
Spanish	1		28	34	-
Spanish	1		33	38	42
Spanish	1		33	38	-
Spanish	1		20	27	33
Spanish	1		20	27	-
English		1	43	23	36
English		1	43	23	-

But I'm guessing that could be down to a fault somewhere else in my script... Hopefully it won't affect my calculations.

Thanks once again for your help.

Best wishes,

Jess