3 Replies Latest reply: May 24, 2012 7:25 AM by Evelien Morel

# Left join twice into same field

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:

'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

[C:\QlikView\QV Storage\DATA\QVD\BKNF18.QVD]

(qvd);

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

Directory;

Left Join (BKNF18)

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)

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

Evelien

• ###### Left join twice into same field

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.

• ###### Re: Left join twice into same field

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

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

[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]);

BKNF18:

...

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

• ###### Re: Left join twice into same field

Hi teempi,

thanks a lot for your help!