Issue with JOIN

[Anonymous]

  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

[sunny_talwar]

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

[Anonymous]

i did. Table B coming from qvd

[sinanozdemir]

Hi,

The below works

And produces the expected result:

[maxgro]

maybe you have a flag field in the qvd?

Directory;

A:

LOAD A, B,

     C, D,

     flag

FROM

Book1.xlsx

(ooxml, embedded labels, table is excel);

B:

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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?

[sunny_talwar · Accepted solution]

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;

[sunny_talwar]

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.