Left Join Duplicates

[#1]

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

[sfatoux72]

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

[sunny_talwar]

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?

[sfatoux72]

Hi Sunny,

this time I answerd before you     but it was not easy

It seems that we had made the same reflexion