Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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];
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:
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: