Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
evelien_morel
New Contributor

Script Where if

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Script Where if

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
MVP
MVP

Re: Script Where if

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
evelien_morel
New Contributor

Re: Script Where if

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

MVP
MVP

Re: Script Where if

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
evelien_morel
New Contributor

Re: Script Where if

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!

Community Browser