topic Re: Left Join Duplicates in QlikView

Left Join Duplicates

Wed, 25 Nov 2020 16:16:04 GMT

I need some assistance please. Loading the following, I get a Table1 with each line duplicated 3 times because the DOCNO appears 3 time in table2. Any idea how I can eliminate the duplicates?

Thank you.

Table1:

LOAD DOCNO,

COST_AMOUNT,

ENTRY,

GLNO,

MONTH,

PERIOD,

PROJNO,

REF,

SALE_AMOUNT,

TRANS_DATE,

YEAR

FROM

(ooxml, embedded labels, table is GL);

Left Join Table1:

Table2:

LOAD DOCNO,

ACCNO,

REP,

STOCKNO

FROM

(ooxml, embedded labels, table is Stock);

Re: Left Join Duplicates

sunny_talwar — Mon, 10 Oct 2016 15:34:39 GMT

May be do applymap instead of join here

Don't join - use Applymap instead

Re: Left Join Duplicates

sfatoux72 — Mon, 10 Oct 2016 16:07:07 GMT

Hi,

If DOCNO appears 3 time in table2 with same combination for other fields, you must simply add a DISTINCT

Left Join Table1:

Table2:

LOAD DISTINCT DOCNO,

ACCNO,

REP,

STOCKNO

FROM

(ooxml, embedded labels, table is Stock);

Re: Left Join Duplicates

Mon, 10 Oct 2016 16:40:25 GMT

Thank you.

Below is my resultant table. You see the DOCNO is the same but it duplicates for each STOCKNO.

Re: Left Join Duplicates

sunny_talwar — Mon, 10 Oct 2016 16:50:23 GMT

Which of the three STOCKNO would you like to see in your resultant table?

Re: Left Join Duplicates

Mon, 10 Oct 2016 17:00:28 GMT

Thank you for your assistance.

I would like to see:

SERV_INTERFORM with REVENUE = 113 232

100200 with REVENUE = 9 020

110200 with REVENUE = 20 335

SERVE INTERFORM with COST OF SALE = 65 275

100200 with COST OF SALE = 11 742

Re: Left Join Duplicates

sunny_talwar — Mon, 10 Oct 2016 17:15:04 GMT

But I guess how did you decide this? I mean we need to understand the logic so that we can provide a solution which might work in all cases. You probably need to provide a sample app or sample data to help us help you

Re: Left Join Duplicates

Mon, 10 Oct 2016 19:16:57 GMT

Hi, I would send you the qvw and the excel if I I could work out how to upload it.

Re: Left Join Duplicates

sunny_talwar — Mon, 10 Oct 2016 19:44:06 GMT

Just what you need

Uploading a Sample

Re: Left Join Duplicates

Mon, 10 Oct 2016 20:04:27 GMT

Please refer to the attached.

Re: Left Join Duplicates

sfatoux72 — Mon, 10 Oct 2016 20:12:20 GMT

You dont'give us a lot of explanation, It's difficult to help you.

But based on your resultant table and of the result that you expect, it seems that there is one field that match with your need :

GLNO seems to be linked with KPI type (3 first number) and with STOCKNO (3 last number):

110... => Revenue, 150... => Cost

...060 => SERV-INTERFORM, ...219 => 100200, ...301 => 110200

If it's the case, you must add an information to do the link correctly.

For example:

map1:

mapping LOAD *

Inline [

GLNO_Part2, STOCKNO

060, SERV-INTERFORM

219, 100200

301, 110200

] ;

Table1:

LOAD DOCNO,

COST_AMOUNT,

ENTRY,

GLNO,

ApplyMap('map1', Right(GLNO, 3)) As STOCKNO

MONTH,

PERIOD,

PROJNO,

REF,

SALE_AMOUNT,

TRANS_DATE,

YEAR

FROM

(ooxml, embedded labels, table is GL);

Left Join Table1:

Table2:

LOAD DOCNO,

ACCNO,

REP,

STOCKNO

FROM

(ooxml, embedded labels, table is Stock);

Re: Left Join Duplicates

sunny_talwar — Mon, 10 Oct 2016 20:23:07 GMT

Is GLNO associated to STOCKNO in some way? Meaning is 110060 = SERV_ITERFORM, 110291 = 100200, and 110301 = 110200? There needs to be some relation for us to understand how one row from one table is associated to another row in the other table?

Re: Left Join Duplicates

sfatoux72 — Mon, 10 Oct 2016 20:36:26 GMT

Hi Sunny,

this time I answerd before you but it was not easy

It seems that we had made the same reflexion