Left join twice into same field

[Anonymous]

Hi all,

I have made 2 left joins. The problem I have is that those 2 joins should put data in the same field "Functional Split"

In Excel, I used to make this with a vlookup function, combined with an iferror.

IFERROR(VLOOKUP(I22504;'Third Party Freight Costs'!A:B;1;FALSE));VLOOKUP(P22504;'Cost Centers'!A:E;5;FALSE))

I have no idea how to make this work in QV, but what I have tried doesn't work.

Here you find the code:

BKNF18:

LOAD

     'AC'                as "Actuality",

     Bedrijf,

     Journaal,

     Boekjaar,

     Boekstuknummer,

     Boekingslijn,

     Boekingslijn2,

     Period,

     [Account Number],

     Century,

     Year,

     Period2,

     Subledger,

     [Cost Center],

     [Amount Transaction Curr],

     [Amount EUR Curr],

     [D/C],

     key_tgrek,

     If(Bedrijf = 'VIT', 'GRV', 'GRS') as key_GR,

     If(Bedrijf = 'VIT', 'KPV', 'KPS') as key_KP,

     [Account Number] & '-' &  If(Bedrijf = 'VIT', 'GRV', 'GRS')                as key_GR_Account,

     [Cost Center] & '-' & If(Bedrijf = 'VIT', 'KPV', 'KPS')                    as key_KP_CostCenter,

     Bedrijf & '-' & [Cost Center]                                                as "Comp-Cost"

FROM

(qvd);

// Functional Split Cost Center ==========================================================================

Directory;

Left Join (BKNF18)

LOAD

     Company & '-' & RKNR06            as "Comp-Cost",

     Company,

     RKNR06,

     [Functional Split]                as "Functional Split"

FROM

[..\QvDATA\AAA SQL (DETAIL COST) VIT + SNS + NUSC 2012 (NEW-original) Prep BU Follow up (TO DO) EMO.xlsx]

(ooxml, embedded labels, header is 1 lines, table is [Cost Centers]);

// Functional Split Account Number =========================================================================

Directory;

Left join (BKNF18)

LOAD

     [Account Number],

     [Functional Split]            as "Functional Split"

FROM

[..\QvDATA\AAA SQL (DETAIL COST) VIT + SNS + NUSC 2012 (NEW-original) Prep BU Follow up (TO DO) EMO.xlsx]

(ooxml, embedded labels, table is [Third Party Freight Costs]);

Any help would be welcome!

Thanx in advace,

Evelien

[Anonymous]

Hi,

since there is no answer to my question, I must be asking a difficult one.

If anyone has any idea, I still have found no solution.

Thanks in advance!

[teempi · Accepted solution]

Hello,

It's certainly doable but I think we need a little more information before we can help Do you want the join to happen in a certain order (for example if nothing is joined from the first table, join from the second)? Is joining multiple values allowed in case there is more than one match etc?

Assuming you want only one value and the first table is higher in priority, you could use applymap to "join" the data. Something like this might work:

CostCenterMap:

MAPPING

LOAD

     Company & '-' & RKNR06            as "Comp-Cost",

     [Functional Split]                as "Functional Split"

FROM

[..\QvDATA\AAA SQL (DETAIL COST) VIT + SNS + NUSC 2012 (NEW-original) Prep BU Follow up (TO DO) EMO.xlsx]

(ooxml, embedded labels, header is 1 lines, table is [Cost Centers]);

AccountMap:

MAPPING

LOAD

     [Account Number],

     [Functional Split]            as "Functional Split"

FROM

[..\QvDATA\AAA SQL (DETAIL COST) VIT + SNS + NUSC 2012 (NEW-original) Prep BU Follow up (TO DO) EMO.xlsx]

(ooxml, embedded labels, table is [Third Party Freight Costs]);

And when loading the fact table use something like this:

BKNF18:

LOAD

...

Applymap('CostCenterMap', Bedrijf & '-' & [Cost Center], Applymap('AccountMap', [Account Number], 0)) AS [Functional Split],

...

This will try to map a value from the first mapping table and if no match is found, it uses the second table. 0 is set if no match is found from either table.

I hope this helps!

-Teemu

[Anonymous]

Hi teempi,

thanks a lot for your help!

Your assumption about the order of joining was correct.

It worked perfectly.