Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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;
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
Hello Sunny,
Great! And is it OK to use first Concatenate between both tables?
Thanks!
First concatenate? What does that mean?
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?
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...
Oh I see... And would you recommend to achieve that? Thanks!
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;