Load Script loading duplicate records

[bruce_sorge]

Greetings,

Can anyone tell me why this load script is loading duplicate records - like thousands of them? My table only has 7,444 records, but Qlik is loading over 800K records. Obviously this is messing up one of my tables when I select a specific item in a filter, and it gives me multiple results that are all duplicates.

[SEARCH_RESULTS_DISPLAY]:
Load Distinct
Purgechar(Subfield(SubField(RC_LIST,'|'),'~',1),'.') as [RC_LIST],
[ABSTRACT_ID] AS [SRDAbstractID],
[EPA_ID],
[ABS_TYPE],
[ABS_TITLE],
[FUNDING_AMT],
Date([PROJ_START_DT], 'MMMM-DD-YYYY') as SRDProjectStartDate,
Date([PROJ_END_DT], 'MMMM-DD-YYYY') as SRDProjectEndDate,
Date([EXT_END_DT], 'MMMM-DD-YYYY') as SRDProjExtDt,
[RFA],
Purgechar(Subfield(SubField(PRI_PI,'|'),'~',1),'.') as PrimaryPI,
PurgeChar(Subfield(Subfield(CO_PI,'|'),'~',1),'.') as CoPI,
[RPTS],
[TOTAL_PUBS],
[PUBS_JOURNAL],
[PUBS_BOOKS],
PurgeChar(SubField(SubField(PRI_INST,'|'),'~',1),'.') as PriInst,
PurgeChar(SubField(SubField(INST,'|'),'~',1),'.') as Inst,
PurgeChar(SubField(SubField(PO,'|'),'~',1),'.') as PO,
PurgeChar(SubField(SubField(PRI_STATE,'|'),'~',1),'.') as PriState,
STATE,
PurgeChar(SubField(SubField([PRI_REGION],'|'),'~',1),'.') as PrimaryRegion,
[REGION],
PurgeChar(SubField(SubField([PRI_LOCATION],'|'),'~',1),'.') as PrimaryLocation,
PurgeChar(SubField(SubField([LOCATION],'|'),'~',1),'.') as AltLocation,
[CONG_DIST],
[GPRA_GOALS],
[DEFAULT_SORT_ORDER],
[SBIR_PHASE] AS [SRDSBIRPhase],
[CTR_NAME],
[CTR_DIR_CONTACT_ID],
[STAR],
[MAIN_ABSTRACT_ID] AS [SRDMainAbstractID],
[CONGRESS_MANDATED] AS [SRDCongressMandate],
[ALTERNATE_EPA_ID] AS [SRDAlternateEPAID],
[FUNDING_PROGRAM],
[DOI],
[PROJ_DESCR],
[KW_IF_ANY],
[FY_OF_FUNDING],
[NOTES];
Select "RC_LIST",
"ABSTRACT_ID",
"EPA_ID",
"ABS_TYPE",
"ABS_TITLE",
"FUNDING_AMT",
"PROJ_START_DT",
"PROJ_END_DT",
"EXT_END_DT",
"RFA",
"PRI_PI",
"CO_PI",
"RPTS",
"TOTAL_PUBS",
"PUBS_JOURNAL",
"PUBS_BOOKS",
"PRI_INST",
"INST",
"PO",
"PRI_STATE",
"STATE",
"PRI_REGION",
"REGION",
"PRI_LOCATION",
"LOCATION",
"CONG_DIST",
"GPRA_GOALS",
"DEFAULT_SORT_ORDER",
"SBIR_PHASE",
"CTR_NAME",
"CTR_DIR_CONTACT_ID",
"STAR",
"MAIN_ABSTRACT_ID",
"CONGRESS_MANDATED",
"ALTERNATE_EPA_ID",
"FUNDING_PROGRAM",
"DOI",
"PROJ_DESCR",
"KW_IF_ANY",
"FY_OF_FUNDING",
"NOTES"
FROM "NCERDB"."SEARCH_RESULTS_DISPLAY";

Thanks

Bruce

[rubenmarin · Accepted solution]

Hi, subfield without a 3rd parameter creates a duplicate record for each value it splits.

In explame: SubField(RC_LIST,'|') -> If there are 4 '|' it will create 5 records, one for each splitted value.

And if you have many of this Sufbfield without a 3rd parameter it creates a cartesian produt of all possible duplicates.

[bruce_sorge]

Thanks, I was not aware of this issue. I added another parameter and it solved my issue.