Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Merge Data and then join to another table

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Merge Data and then join to another table

Try like:

Load

          ID,

          Location

From <Table1>;

Join

Load

          ID,

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

From <Table2> Group By ID;

5 Replies
MVP
MVP

Re: Merge Data and then join to another table

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

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;

2.

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;

Not applicable

Re: Merge Data and then join to another table

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

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.

Not applicable

Re: Merge Data and then join to another table

THANKS YOU ARE THE BEST !

Community Browser