Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jessica_webb
Creator III
Creator 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

1 Solution

Accepted Solutions
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

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

View solution in original post

10 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
jonathandienst
Partner - Champion III
Partner - Champion III

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
Partner - Specialist III
Partner - Specialist III

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

Hi,

Hope this helps

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

Many Thanks

Paul

jessica_webb
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Thanks for all suggestions. The IntervalMatch function worked perfectly.

jessica_webb
Creator III
Creator III
Author

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
Partner - Specialist III
Partner - Specialist III

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
Creator III
Creator III
Author

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