Duplicate data in table created in script

[#1]

Hello,

I'm having several issues with this script.

The first is that when I load this script, the IDs that get the Account No. added from the Peek(), get duplicated in the resulting data.

Domain	Account	ID	Account No.
Company A	Sales	6156	1521
Company A	Sales	6259	1521
Company A	Sales	6259	-
Company A	Sales	6459	1521
Company A	Sales	6459	-

I need to remove the data for ID that doesn't have the Account No. associated.

The second is that the Table NHDBID doesn't show up as its own table in Qlik Sense.  I don't know if that's adding to the problem, but I thought that the script would have created a Table called NHDBID, and maybe using Drop Table NHDB would fix the issue above.

The script I'm referring to is below:

NHDB:

Load

[ID],

Domain,

Account

resident ID;

Left Join

Load

[ID],

[Account No.]

Resident Sales;

NHDBID:

Load

Domain,

[ID],

If(IsNull([Account No.]),Peek([Account No.]),[Account No.]) as [Account No.],

Account

Resident NHDB

Order By Domain, [ID];

[swuehl · Accepted solution]

Your tables NHDB and NHDBID gets auto-concatenated, due to the same field names in both tables.

Use NOCONCATENATE LOAD prefix to prevent QS from doing this, then drop table NHDB:

NHDB:

Load

[ID],

Domain,

Account

resident ID;

Left Join

Load

[ID],

[Account No.]

Resident Sales;

NHDBID:

NOCONCATENATE Load

Domain,

[ID],

If(IsNull([Account No.]),Peek([Account No.]),[Account No.]) as [Account No.],

Account

Resident NHDB

Order By Domain, [ID];

DROP TABLE NHDB: