topic Re: Duplicating values in tables in QlikView

Duplicating values in tables

AH — Wed, 20 May 2015 14:09:55 GMT

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

Re: Duplicating values in tables

robert_mika — Wed, 20 May 2015 14:33:35 GMT

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

Re: Duplicating values in tables

AH — Wed, 20 May 2015 14:44:16 GMT

Hi Mika,

Thanks for the reply.

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

Thanks,

Ahmed100

Re: Duplicating values in tables

robert_mika — Wed, 20 May 2015 15:00:30 GMT

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

Re: Duplicating values in tables

AH — Thu, 21 May 2015 02:36:40 GMT

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

Re: Duplicating values in tables

PrashantSangle — Thu, 21 May 2015 04:01:33 GMT

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

Re: Duplicating values in tables

Thu, 21 May 2015 05:15:22 GMT

use Distinct in the table:

ex.

TblName1:

Load DISTINCT

fieldnme1,

fieldnme2

from....

TblName2:

noConcatenate(TblName1)

Load DISTINCT

fieldname2,

filedname3

from

...