topic Re: Load 2 QVD's into one table in QlikView

Load 2 QVD's into one table

Anonymous — Wed, 19 Jun 2013 21:21:58 GMT

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;

Re: Load 2 QVD's into one table

Wed, 19 Jun 2013 22:16:36 GMT

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.

Re: Load 2 QVD's into one table

Anonymous — Wed, 19 Jun 2013 22:29:08 GMT

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.

Re: Load 2 QVD's into one table

Wed, 19 Jun 2013 22:32:18 GMT

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.

Re: Load 2 QVD's into one table

Anonymous — Wed, 19 Jun 2013 22:43:12 GMT

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

Re: Load 2 QVD's into one table

Wed, 19 Jun 2013 22:46:19 GMT

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

Re: Load 2 QVD's into one table

Anonymous — Wed, 19 Jun 2013 22:52:51 GMT

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

Re: Load 2 QVD's into one table

Wed, 19 Jun 2013 23:03:02 GMT

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

Re: Load 2 QVD's into one table

Anonymous — Wed, 19 Jun 2013 23:12:20 GMT

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

Generic tables must contain at least 3 fields.

Re: Load 2 QVD's into one table

Anonymous — Wed, 19 Jun 2013 23:25:57 GMT

I think that worked! Thanks for your help!

Re: Load 2 QVD's into one table

CK_WAKE — Fri, 26 Apr 2024 12:34:08 GMT

I will check on the table values