topic Re: Matching Values in Common from 2 Tables in QlikView

Matching Values in Common from 2 Tables

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

Hello,

I have 2 tables, Budget and Actual.

Budget:

- Period (X)

- Amount

- Account (Y)

- Detail

- Qty

- Society (Z)

- 'Budget' AS Data_Source

Actual:

- Period (X)

- Amount

- Account (Y)

- Indicator

- Society (Z)

- 'Actual' AS Data_Source

What I need is to generate a KEY between Budget and Actual with fields PERIOD (X), ACCOUNT (Y) and SOCIETY (Z).

I'd need to concatenate all this data but having only the values that match in the generated KEY.

Is there any way to do that?

Thank you!!!

Re: Matching Values in Common from 2 Tables

sunny_talwar — Tue, 29 Aug 2017 13:39:03 GMT

Key can be created using AutoNumber or just simply using & between the fields

LOAD AutoNumber(PERIOD (X)&'|'&ACCOUNT (Y)&'|'&SOCIETY (Z)) as Key

and then to bring only the matching stuff you can use Where Exists() or Inner Keep

Re: Matching Values in Common from 2 Tables

microwin88x — Tue, 29 Aug 2017 13:45:40 GMT

Hello Sunny,

Great! And is it OK to use first Concatenate between both tables?

Thanks!

Re: Matching Values in Common from 2 Tables

sunny_talwar — Tue, 29 Aug 2017 13:48:43 GMT

First concatenate? What does that mean?

Re: Matching Values in Common from 2 Tables

microwin88x — Tue, 29 Aug 2017 13:51:58 GMT

I mean I do:

FACT:

LOAD AutoNumber(PERIOD (X)&'|'&ACCOUNT (Y)&'|'&SOCIETY (Z)) as Key,

- Period (X)

- Amount

- Account (Y)

- Detail

- Qty

- Society (Z)

- 'Budget' AS Data_Source

FROM Budget;

CONCATENATE(FACT)

LOAD AutoNumber(PERIOD (X)&'|'&ACCOUNT (Y)&'|'&SOCIETY (Z)) as Key,

- Period (X)

- Amount

- Account (Y)

- Indicator

- Society (Z)

- 'Actual' AS Data_Source

FROM Actual

WHERE Exists(AutoNumber(PERIOD (X)&'|'&ACCOUNT (Y)&'|'&SOCIETY (Z));

Something like that?

Re: Matching Values in Common from 2 Tables

sunny_talwar — Tue, 29 Aug 2017 14:03:10 GMT

This can work, but the only issue can be that it will only check the second table for matching values from first table. BUDGET Table won't check if ACTUAL has matching values or not...

Re: Matching Values in Common from 2 Tables

microwin88x — Tue, 29 Aug 2017 14:13:22 GMT

Oh I see... And would you recommend to achieve that? Thanks!

Re: Matching Values in Common from 2 Tables

sunny_talwar — Tue, 29 Aug 2017 14:22:23 GMT

May be this

FACT:

LOAD AutoNumber(PERIOD (X)&'|'&ACCOUNT (Y)&'|'&SOCIETY (Z)) as Key,

- Period (X)

- Amount

- Account (Y)

- Detail

- Qty

- Society (Z)

- 'Budget' AS Data_Source

FROM Budget;

Actual:

Inner Keep(FACT)

LOAD AutoNumber(PERIOD (X)&'|'&ACCOUNT (Y)&'|'&SOCIETY (Z)) as Key,

- Period (X)

- Amount

- Account (Y)

- Indicator

- Society (Z)

- 'Actual' AS Data_Source

FROM Actual;

Concatenate(FACT)

LOAD *

Resident Actual;

DROP Table Actual;