topic Re: Merge Data and then join to another table in QlikView

Merge Data and then join to another table

Tue, 01 Jul 2014 04:49:58 GMT

Good morning too all,

how can I resolve the following problem:

content Table 1:

ID
Location
etc.

content Table 2:

ID
freight cost
freight cost type

Nearly every ID in Table2 is duplicated because it has two different freight cost types.
Now I want to have a sum of both and then left join to the table 1. When I just do the left join then I have also the duplications in Table 1, which I dont want to have.

Who know how to solve this?r

I appreciate every kind of help!

Thanks and regards

Martin

Re: Merge Data and then join to another table

tresB — Tue, 01 Jul 2014 04:56:23 GMT

Try like:

Load

ID,

Location

From <Table1>;

Join

Load

ID,

Sum([Freight Cost]) as [Freight Cost]

From <Table2> Group By ID;

Re: Merge Data and then join to another table

its_anandrjs — Tue, 01 Jul 2014 05:08:20 GMT

You can try like

1.Without joining them and leave with association

Load

ID,

Location

From Table1;

Load DISTINCT

ID,

freight cost type

[Freight Cost]

From Table2 Group By ID;

Load

ID,

Location

From Table1;

Join

Load

ID,

freight cost type,

Sum([Freight Cost]) as [Freight Cost]

From Table2 Group By ID,freight cost type;

Re: Merge Data and then join to another table

Tue, 01 Jul 2014 05:33:15 GMT

LOAD

Sum([Freight Cost]) as [Freight Cost],

[ÌD] as

FROM

(ooxml, embedded labels, header is 1 lines, table is Fracht);

where I have to add the GROUP BY function? And do I have to say Group By ID or Group By Code cause I renamed it?

Re: Merge Data and then join to another table

its_anandrjs — Tue, 01 Jul 2014 05:38:33 GMT

You have to write like

LOAD

Sum([Freight Cost]) as [Freight Cost],

[ÌD] as [Code]

FROM

(ooxml, embedded labels, header is 1 lines, table is Fracht)

Group By [ÌD];

If there is only one dimension then try like this way.

Re: Merge Data and then join to another table

Tue, 01 Jul 2014 05:40:47 GMT

THANKS YOU ARE THE BEST !