Duplicating values in tables

[AH]

Hi,

I have two table that are connected by the key "InstrumentSerialNumber". One table "Alarm" that has 4 rows and table "Transition" that has 197 rows. when i include columns from both table in one column then row goes upto 788. And the colums from "Alarm" table duplicates with the "Transition" table although it has only 4 rows in the table.

Is there any solution i can allpy to prevent the duplicating?

Thanks,

Ahmed100

[robert_mika]

Alarm:

LOAD

     InstrumentSerialNumber,

     AlarmDt,

     StateNo as AlarmStateNo,

     AlarmID

FROM

[..\Desktop\SC.xlsx]

(ooxml, embedded labels, table is Alarm)

;

noConcatenate

LOAD KitSNo,

     KitLotNo,

     InstrumentSerialNumber,

     StateTransDT,

     StateNo,

     WBProcessed,

     TXVol

FROM

[..\Desktop\SC.xlsx]

(ooxml, embedded labels, table is Transition);

[AH]

Hi Mika,

Thanks for the reply.

The combination of the two table still having 788 rows. and AlarmID is duplicating.

Thanks,

Ahmed100

[robert_mika]

Try this:

Alarm:

  LOAD

     InstrumentSerialNumber,

     AlarmDt,

     StateNo,

     AlarmID

FROM

[..\Desktop\SC.xlsx]

(ooxml, embedded labels, table is Alarm)

;

right keep(Alarm)

LOAD KitSNo,

     KitLotNo,

     Lookup( 'AlarmDt','InstrumentSerialNumber',InstrumentSerialNumber,'Alarm') as AlarmDt,

      Lookup( 'StateNo','InstrumentSerialNumber',InstrumentSerialNumber,'Alarm') as AlarmStateNo, 

       Lookup( 'AlarmID','InstrumentSerialNumber',InstrumentSerialNumber,'Alarm') as AlarmID,

     StateTransDT,

     StateNo,

     WBProcessed,

     TXVol

FROM

[..\Desktop\SC.xlsx]

(ooxml, embedded labels, table is Transition);

drop table Alarm

[AH]

Hi Mika,

If i pull AlarmID in the table then it seems that is duplicating. But we have only 4 rows in Alarm table. Doesnt look like its giving me the exact data i am looking for.

Thanks,

Ahmed100

[PrashantSangle]

Hi,

There is no unique key field which gives you unique data from 2 table .

When you associate both table on InstrumentSerialNumber then both table have same data for InstrumentSerialNumber field there for you are getting 4*197=788 records.

For getting unique data first you have to find which field gives you unique data.

Hope you got what i am trying to suggest you.

Regards

[#7]

use Distinct in the table:

ex.

TblName1:

Load DISTINCT

fieldnme1,

fieldnme2

from....

TblName2:

noConcatenate(TblName1)

Load DISTINCT

fieldname2,

filedname3

from

...