Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
jessica_webb
Contributor III

Join table based on minimum values

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_predIs_AIs_BIs_CPrior_ABPrior_C
Spanish12834
Spanish13338
Spanish12027
English14354
English12927

I then have another table (TABLE_NEW) which I would like to join to TABLE_INFO, with the following fields:

Qual_predMinMaxOutcome
Spanish162033
Spanish212537
Spanish263040
Spanish313542
English182128
English222536
English262938

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_predIs_AIs_BIs_CPrior_ABPrior_COutcome
Spanish1283440
Spanish1333842
Spanish1202733
English1432336
French11729

Any help appreciated - even just a hint as to what direction I should go in!

Many thanks in advance,

Jess

Tags (2)
1 Solution

Accepted Solutions
MindaugasBacius
Valued Contributor III

Re: Join table based on minimum values

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:

Screenshot_1.jpg

The app is attached.

Also, please check the Help information - it analysis practicly the same issue as your's:

IntervalMatch ‒ QlikView

10 Replies

Re: Join table based on minimum values

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP
MVP

Re: Join table based on minimum values

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MindaugasBacius
Valued Contributor III

Re: Join table based on minimum values

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:

Screenshot_1.jpg

The app is attached.

Also, please check the Help information - it analysis practicly the same issue as your's:

IntervalMatch ‒ QlikView

paulferguson80
New Contributor III

Re: Join table based on minimum values

Hi,

Hope this helps

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

Many Thanks

Paul

jessica_webb
Contributor III

Re: Join table based on minimum values

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
Contributor III

Re: Join table based on minimum values

Thanks for all suggestions. The IntervalMatch function worked perfectly.

jessica_webb
Contributor III

Re: Join table based on minimum values

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
Valued Contributor III

Re: Join table based on minimum values

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:

Screenshot_1.jpg

jessica_webb
Contributor III

Re: Join table based on minimum values

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