problem with join statement

[rutgerzwaal]

Hi,

I have a problem with joining data.

I want to join the data from a previous year to the current contract.

We work with weekly exports so we extract the week number from the filename.

The join works for the first week, but for the second week (and al following weeks) the join doesn't work.

I can't figure out what's causing this problem.

Any ideas?

Best regards,

Rutger

[ali_hijazi]

you need to concatenate the tables not join them

your script should be something like this:

Data:

LOAD Client,

     Budget,

     num(right(FileBaseName(),2)) as Week

FROM

[*.xls]

(biff, embedded labels, table is data$)

Where jaar=2015;

concatenate(Data)

LOAD Client,

     Budget as [Budget LY],

     num(right(FileBaseName(),2)) as Week

FROM

[*.xls]

(biff, embedded labels, table is data$)

Where jaar=2014 and status = 'contract';

But may you please elaborate what exactly you want to achieve?

[rutgerzwaal]

Hi,

Thank you for replying.

If you look at the table for week 14 you see that budget and budget LY are on the same row, but for the other two weeks they're not.

Like week 14 is how I want the data to be.

And why does this only work for week 14 in my example. I don't know.

When using concatenate the rows are not joined, and I want to do as much in the load script as possible.

[Gabriel]

Hi,

Do  you want to have a full list of weekly report then Concatenate the file with where clause. If this is not what you want to achieve, then explain what you want to achieve. Otherwise see below

DATA:

LOAD
      TRIM(Client) AS Client,
    TRIM(jaar) AS jaar,
     TRIM(Budget) AS Budget
      ,num(right(FileBaseName(),2)) as Week
     ,status
FROM

[*.xls]

(biff, embedded labels, table is [data$])

Where status = 'contract'
;
Concatenate(DATA)

LOAD
      TRIM(Client) AS Client,
       TRIM(jaar) AS jaar,
         TRIM(Budget) as Budget
          ,num(right(FileBaseName(),2)) as Week
          ,status
FROM

[*.xls]

(biff, embedded labels, table is [data$])

Where status = 'contract'
;

[ali_hijazi]

you want to join what based on what?

you have different weeks

the common fields are year (jaar) and client no?

then if you insist on joining you can join based on common year and client

otherwise you need to concatenate the tables

[rutgerzwaal]

If I use concatenate the contracts of last year will be on a separate row, but i want them to be on one row.

Let me explain why.

On a given moment we want to see how much budget in on contract. What we also want to know is how high those contracts were last year (Budget LY).

If we would use concatenate then we could check how much was on contract last year on the same moment, but we want to know how much was on contract for the clients of the current selection.

In my opinion the join statement would make for an easy solution.

Hope this makes any sense.