Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

evelien_morel
Not applicable

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:

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

1 Solution

Accepted Solutions
teempi
Not applicable

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

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

3 Replies
evelien_morel
Not applicable

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.

Thanks in advance!

teempi
Not applicable

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

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

evelien_morel
Not applicable

Re: Left join twice into same field

Hi teempi,

thanks a lot for your help!

Your assumption about the order of joining was correct.

It worked perfectly.