topic linking tables in QlikView

linking tables

Tue, 29 Nov 2011 14:22:46 GMT

I want to filter a certain table by field which isnt in that particular table. Ive tried to concatenate it in and create a resident table containing all the values.

This is still giving me issues, the desc field is null in the material_1 table.

[material]:
NOCONCATENATE
LOAD product,
desc,
product & ' - ' & desc as group
FROM [test.csv];

CONCATENATE LOAD
product,
val
RESIDENT history;

material_1:
NOCONCATENATE LOAD
     product,
     desc,
     product & ' - ' & desc as group,
     val
RESIDENT material
where val > '5';

drop table material;

Re: linking tables

swuehl — Tue, 29 Nov 2011 15:02:29 GMT

brianm10,

your lines concatenated to material in your second load won't have desc values, and your lines loaded in the first load won't have val values.

So if you use where val>5 in material_1 load, I think you lose all lines from the first load and filter only second load lines, which don't have desc values as said.

Maybe you want to use a join instead of the concatenation in your second load, like

...

LEFT JOIN LOAD

product,

val

RESIDENT history;

...

Not sure if this is what you want or need though.

Regards,

Stefan

linking tables

Tue, 29 Nov 2011 15:31:57 GMT

that seems to have worked, but when I view the results on my table diagram then im seeing alot of duplicated entries.

however, everything seems to be ok on my sheet

linking tables

swuehl — Tue, 29 Nov 2011 15:56:24 GMT

In your second load from history, do you have multiple val values per product?

You may consider applying the where clause to the second load already.

Could you upload small samples of your input data here?

linking tables

Tue, 29 Nov 2011 16:06:22 GMT

there's only one value per product, I've applied to clause to the second load but still get duplicated items

linking tables

Tue, 29 Nov 2011 16:25:54 GMT

Apologies but i cant post the report.

Im trying to join data from 2 seperate files, one a QVD and the other a CSV.

linking tables

swuehl — Tue, 29 Nov 2011 16:30:16 GMT

For now, I could only imagine that your left join will result in duplicates if your field names don't match. Could you stop after doing the second load (use comments or exit script statement) and check the resulting table and fields? Anything suspicious?

linking tables

bismart — Tue, 29 Nov 2011 16:36:30 GMT

Try this...

material:
LOAD

     product,
     desc,
     product & ' - ' & desc as group
FROM [test.csv];

LEFT JOIN LOAD
product,
val
RESIDENT history;

material_1:
NOCONCATENATE LOAD
     product,
     desc,
     group,                           // Field in previously loaded table material is 'group'
     val
RESIDENT material
where val > '5';                    // If val is numeric then > 5 instead of '5'

drop table material;

linking tables

Tue, 29 Nov 2011 16:37:01 GMT

After the second load things are joining up correctly, however the val figure is null.

[material]:
NOCONCATENATE
LOAD product,
desc,
product & ' - ' & desc as group
FROM [test.csv];

LEFT JOIN LOAD
product,
g_prod as group,
val
RESIDENT bbavi_history
WHERE val > '05';

drop table material;

Re: linking tables

bismart — Tue, 29 Nov 2011 16:37:39 GMT

Try this...

material:
LOAD

     product,
     desc,
     product & ' - ' & desc as group
FROM [test.csv];

LEFT JOIN LOAD
product,
val
RESIDENT history;

drop table material;