topic Re: Issue with JOIN in QlikView

Issue with JOIN

Anonymous — Wed, 26 Oct 2016 15:30:20 GMT

Hello experts,

I am facing some strange problem with join. All the fields A, B, C, D are loaded in text format in qlikview and getting joined. But somehow join is not working correctly if you can have a look and share your view.

Table A : coming from spreadsheet						Table B: coming from qvd
A	B	C	D	flag		A	B	C	D	Item
300	01	07	43	2		300	01	07	43	S1
300	01	08	43	3		300	01	07	43	S2
						300	01	07	43	S3
						300	01	07	43	S4

TABLE A JOIN TABLE B						Ideally this should be the result
A	B	C	D	flag	Item	A	B	C	D	flag	Item
300	01	07	43	2		300	01	07	43	2	S1
300	01	07	43		S1	300	01	07	43	2	S2
300	01	07	43		S2	300	01	07	43	2	S3
300	01	07	43		S3	300	01	07	43	2	S4
300	01	07	43		S4	300	01	08	43	3
300	01	08	43	3

Thanks

Re: Issue with JOIN

sunny_talwar — Wed, 26 Oct 2016 15:31:27 GMT

Would you be able to provide this sample data in a Excel file?

Re: Issue with JOIN

Anonymous — Wed, 26 Oct 2016 15:38:31 GMT

i did. Table B coming from qvd

Re: Issue with JOIN

sinanozdemir — Wed, 26 Oct 2016 15:44:56 GMT

Hi,

The below works

And produces the expected result:

Re: Issue with JOIN

maxgro — Wed, 26 Oct 2016 15:47:00 GMT

maybe you have a flag field in the qvd?

Directory;

LOAD A, B,

C, D,

flag

FROM

Book1.xlsx

(ooxml, embedded labels, table is excel);

LOAD A, B,

C, D,

Item

FROM

Book1.xlsx

(ooxml, embedded labels, table is qvd);

STORE * from B into B.qvd (qvd);

DROP Table B;

// if I add a non matching flag, I get 6 rows

join (A) load *, null() as flag

from B.qvd (qvd);

Re: Issue with JOIN

Anonymous — Wed, 26 Oct 2016 15:50:50 GMT

No Sinan that is not the case. In principle if format and values are same then join should definitely work but unfortunately that's not happening in this case. I know that the way I have provided data to sunny I load it in qlikview and run it will work. I am just looking for anybody who has come across this experience.

Re: Issue with JOIN

Anonymous — Wed, 26 Oct 2016 16:24:07 GMT

One more thing that I have noticed if I don't force join TABLE A and TABLE B and keep them separate in data model and let them associate with each other, I get the correct result. The problem starts when I do join.

A	B	C	D	flag	Item
300	01	07	43	2	S1
300	01	07	43	2	S2
300	01	07	43	2	S3
300	01	07	43	2	S4
300	01	08	43	3

Re: Issue with JOIN

Anonymous — Wed, 26 Oct 2016 18:31:08 GMT

One more observation- join works correctly with a single qvd load but when I load data from a series of qvd using sth like '/2016*', that's where join starts working erroneously. Any comment?

Re: Issue with JOIN

sunny_talwar — Wed, 26 Oct 2016 18:33:10 GMT

Yes, if you are using a direct join using a wildcard in your load, it will not work. This is what you need to do in that case

Table:

LOAD *

FROM Path/2016*;

Left Join (SomeTable)

LOAD *

Resident Table;

DROP Table Table;

Re: Issue with JOIN

sunny_talwar — Wed, 26 Oct 2016 18:34:31 GMT

I cannot find where, but I heard saw (in one of the posts)hic‌ mentioning that loading the files using a loop is a better option then using wildcards. If I remember correctly, he mentioned that he barely use wildcard anymore.

Re: Issue with JOIN

Anonymous — Wed, 26 Oct 2016 18:43:24 GMT

I agree * does not work efficiently always. I hardly use * but it was not my main application I was just resizing data for load when I came across this issues.