Load 2 QVD's into one table

[Anonymous]

I'm trying to combine 2 different qvd's into one table.  Here is what I have.

INVOICE:
LOAD
hdr_key,
entity as [entity code],
[cash discount amt] as [amount],
[cash discount gl account] as [gl account]
FROM $(vPathNameLayer1)inc-hdr-all.qvd(qvd);

JOIN
LOAD
hdr_key,
line_key,
[line gpsa] as [amount],
[line item gl account] as [gl account]
FROM $(vPathNameLayer1)inc-line-all.qvd(qvd);

When I do this then entity code is not showing up for anything from the second join.  How do I accomplish this?  Here is what I would do in SQL but it doesn't like the syntax.

JOIN

LOAD

hdr_key,

entity as [entity code],

line_key,

[line gpsa] as [amount],

[line item gl account] as [gl account]

FROM $(vPathNameLayer1)inc-line-all.qvd(qvd) as a,

           $(vPathNameLayer1)inc-hdr-all.qvd(qvd) as b

WHERE a.hdr_key = b.hdr_key;

[#2]

I think your Join is not working since you're renaming different filelds with the same names, and I don't think these have the same information. For instance:

[cash discount amt]  as [amount]     AND  [line gpsa] as [amount]

Since QlikView will use the fields with the same names as keys to perform the join... you'll have to make sure that this fields have the exact same values in order to serve as the keys for the join, otherwise there will be no record that matches between the tables.

[Anonymous]

How would I combine these two different tables but only have one column for these amounts.  At the end I should only have 3 columns. Entity, Amount, GL Account.

[#4]

It depends on the way you want to link your tables but for that result I think you could try the CONCATENATE statement. It will work like a UNION ALL in SQL.

[Anonymous]

I've tried this and still don't get entity for anything from the second load.  Also, I end up with 2 amount columns instead of one.  Do you have any suggestions on another approach I can take?

INVOICE:
LOAD
hdr_key,
entity as [entity code],
[cash discount amt] as [amount],
[cash discount gl account] as [gl account]
FROM $(vPathNameLayer1)inc-hdr-all.qvd(qvd);

Concatenate
LOAD
hdr_key,
line_key,
[line gpsa],
[line item gl account]
FROM $(vPathNameLayer1)inc-line-all.qvd(qvd);

[#6]

INVOICE:
LOAD
hdr_key,
entity as [entity code]
FROM $(vPathNameLayer1)inc-hdr-all.qvd(qvd);

LEFT JOIN (INVOICE)
LOAD
hdr_key,
line_key,
[line gpsa] as [amount],
[line item gl account] as [gl account]
FROM $(vPathNameLayer1)inc-line-all.qvd(qvd);

[Anonymous]

Thank you Steve.  This will not work though because I also need [cash discount amt] from the first load in the same column.

[#8]

In that case you'll need to map the entity values to the second table first, and the use the CONCATENATE to align the fields in the ones you need. If your first load contains all the hdr_key values that exist in the second load, you could do this:

MAP_ENTITY:

MAPPING LOAD

hdr_key,

entity

FROM $(vPathNameLayer1)inc-hdr-all.qvd(qvd);

INVOICE:
LOAD

entity as [entity code]

[cash discount amt] as [amount],

[cash discount gl account] as [gl account]
FROM $(vPathNameLayer1)inc-hdr-all.qvd(qvd);

CONCATENATE

LOAD

APPLYMAP('MAP_ENTITY',hdr_key,'No hdr_key') as [entity code],

[line gpsa] as [amount],

[line item gl account] as [gl account]

FROM $(vPathNameLayer1)inc-line-all.qvd(qvd);

[Anonymous]

I'm not familiar with the APPLYMAP function.  I'm getting this error.

Generic tables must contain at least 3 fields.

[Anonymous]

I think that worked! Thanks for your help!