Join not work out of the box but with a twist?

[#1]

Hello community!

I had a really odd obstacle on my way yesterday with QlikView, when the client was having some load problems with his QlikView application. The main problem was, that the application that once worked, now did not. After my investigation, the main reason for this "not working" thing was that the data did not load correctly for some reason any more.

After a few test load, I saw that the main table did not "exist" during the joins directly. I made so called double load from the same data and then a join and after that everything worked just normally.

Does someone in community now, what is the main reason why the application did not work anymore? Why did I have to make double load and then join? So what I modified in the load, was to add another LOAD statement, that loads all the same data that it did load from the previous statement.

See the code below:

PRODS:
LOAD

@1 AS prod_id,
@2 AS prod_name

FROM
$(Path)prods\*
(txt, utf8, no labels, delimiter is \x1, no quotes);

join (PRODS)
LOAD Distinct
@3 AS prod_id,
@4 AS prod_name
FROM
$(Path)orderitems\*
(txt, utf8, no labels, delimiter is \x1, no quotes);

//left join (PRODS)

// this was previously done with straight join to the PRODS table, but now it did not work

//
PRODMAP:
LOAD @1 AS prod_id,
@3 AS slot_id

FROM
$(Path)productmap\*
(txt, utf8, no labels, delimiter is \x1, no quotes);

// when I do the join here and load from resident, everything works normally
left join (PRODS)
load * Resident PRODMAP;
drop table PRODMAP;

Cheers,

Niko

[sunny_talwar]

Are there multiple files at this location now? May be you had just one file previously and now it is more than one?

PRODMAP:
LOAD @1 AS prod_id,
@3 AS slot_id
FROM
$(Path)productmap\*

[#3]

Hello,

Well in my good guess, because I don't really have the right answer right now. is that there has been multiple files before also. But I think I have to ask if that is the case. But how that can mess around with the Load & join ? Or should that be a for each function there in place and when there has been one file it just loaded the one. And if there has been many files, it just has loaded the first one of them?

So now with the "double load" - we get all the files loaded to the table. I think I now understand a little bit better

Cheers,

Niko

[sunny_talwar]

When you have multiple files, they auto-concatenate to form a single table (assuming all of them have the same set of fields). But if you are joining, the auto-concatenate action might not kick in. It tries to join the first excel file and once it does that, the other once won't join at all because of the left join.

So I think it is work checking if there was only one file before and now you have multiple files.

Also keep an eye on your other join

join (PRODS)
LOAD Distinct
@3 AS prod_id,
@4 AS prod_name
FROM
$(Path)orderitems\*
(txt, utf8, no labels, delimiter is \x1, no quotes);

Check how many files are within this location

[#5]

Hi Sunny,

Yeah I think the problem has been with that it has just loaded one file first that has data. If QlikView has that kinda logic, that it skips the files that has no data even if there is multiple files on the location. Really have to check the files now

Thanks for all the help for now already, helped my thought process too!

Cheers,

Niko

[sunny_talwar]

Great, I am glad I was able to stimulate your thinking.

Best,

Sunny