Matching Values in Common from 2 Tables

[microwin88x]

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

[sunny_talwar]

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

[microwin88x]

Hello Sunny,

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

Thanks!

[sunny_talwar]

First concatenate? What does that mean?

[microwin88x]

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?

[sunny_talwar]

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

[microwin88x]

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

[sunny_talwar · Accepted solution]

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;