Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Load 2 QVD's into one table

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;

9 Replies
Not applicable
Author

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
Not applicable
Author

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.

Not applicable
Author

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
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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

Generic tables must contain at least 3 fields.

Anonymous
Not applicable
Author

I think that worked! Thanks for your help!