Discussion Board for collaboration related to QlikView App Development.
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.
you need to concatenate the tables not join them
your script should be something like this:
num(right(FileBaseName(),2)) as Week
(biff, embedded labels, table is data$)
Budget as [Budget LY],
Where jaar=2014 and status = 'contract';
But may you please elaborate what exactly you want to achieve?
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.
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
LOAD TRIM(Client) AS Client, TRIM(jaar) AS jaar, TRIM(Budget) AS Budget ,num(right(FileBaseName(),2)) as Week ,statusFROM
(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 ,statusFROM
Where status = 'contract';
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
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.