Script Where if

[Anonymous]

Hi all,

i''m trying to left join two tables, but I'm facing problems.

I'm working with data from 3 different companies (VIT, SNS, NUSC).

The data I want to join is different for the 3 companies but comes out of the same table.

Here you see my code:

BKNF18:

LOAD

     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

FROM

(qvd);

// REKF Account) number =========================================================================

left join (BKNF18)

LOAD

    RKNR06 as [Account Number],

    RKNR06,

    RSKD06,

    RKNM06                    as "Account Name"

where if(Bedrijf = "VIT", RSKD06 = "GRV", RSKD06 = "GRS")

;

SQL SELECT *

FROM VITAMEXA.AAADTA.REKF06;

'Bedrijf' is the key field that defines the different companies (VIT, SNS, NUSC)

'RKSD06' is the field that determines to which company the data belongs. (GRV for company 'VIT', GRS for company 'SNS', ...)

When I run this code, I get the message 'Field not found' <Bedrijf>

Any ideas how I get this code running?

Thanks!

[jonathandienst · Accepted solution]

Hi

Bedrijf does not exist in the data returned from your SQL SELECT statement, so you cannot use it here.

I am not sure of your data structure, but based on what you have posted I suggest the following.

• Remove the where-if line from the second LOAD. I suspect that this statement would not be doing what you are expecting anyway, but you cant use Bedrijf here.
• Add this line to the first LOAD (where Bedrijf does exist:

               If(Bedrijf = 'VIT', 'GRV', 'GRS') As RSKD06

Hope that helps

Jonathan

[Anonymous]

Hi Jonathan,

I tried your solution but it doesn't do what I expect it to do.

When the company is 'VIT', then the field RSKD06 is GRV.

For the other companies, the field RSKD06 is blank. I would expect to see GRS there.

If I can get this working, I can make a key field to make the join with the REKF06 table.

What have I done wrong?

Here the adjusted code:

BKNF18:

LOAD

     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 RSKD06

FROM

(qvd);

[jonathandienst]

Evelien

Looks OK to me, so I am not sure why RSKD06 is blank for other values.

Is it possible to post the QVD?

Regards

Jonathan

[Anonymous]

Jonathan,

I got the IF formula to work, I forgot to add it to another table I use.

Because the IF works, I was able to make a key field, so the join to REKF06 also works now.

Thanks for your help!