linking tables

[#1]

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;

[swuehl]

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

[#3]

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

[swuehl]

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?

[#5]

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

[#6]

Apologies but i cant post the report.

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

[swuehl]

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?

[bismart]

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;

[#9]

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;

[bismart]

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;