Hoping someone can assist. I have a dashboard which is loading multiple CSV files from a single file location (Same columns, each file is a daily export). There are three different tables loaded in this way (linked by Applicant ID field).
I have an inline table which details a small number of records (Applicant ID, programme, stage, offer status and SeqNo (contains the value DA - duplicate application) which need to be filtered out as they are duplicates. I've tried join the inline table to the main table and then filtering out the duplicates using a where clause together with a not wildmatch statement but it seemed to only filter out the records in the inline table and not the main table.
Script below:
Table:
//Full Data from automated export
LOAD //ACAD_SESSION as Session,
SubField(ACAD_SESSION, '/',1) & '/1' &SubField(ACAD_SESSION, '/', -1) as Session,
SubField(ACAD_SESSION, '/',1) & '/1' &SubField(ACAD_SESSION, '/', -1) as ComparisonSession,
KeepChar(ACAD_SESSION, '0123456789') and SubField(ACAD_SESSION, '/',1) & '/1' &SubField(ACAD_SESSION, '/', -1) as SessionNum,
Date(Date#(RUN_DATE, 'DD/MM/YYYY'),'DD/MM/YYYY') as Date,
APPLICANT_ID as [Applicant ID],
SCHOOL as School,
PROGRAMME as Programme,
LVL as Level,
MODE_STUDY as Mode,
STAGE as Stage,
PREVIOUS_INST as PreviousInstitution,
DOMICILE as Domicile,
DATE_OF_BIRTH as [Date Of Birth],
MapSubString('FeesMapping',FEE_STAT) as FeeStat,
DISABILITY as Disability,
if(DISABILITY='None','None','Declared') as DisabilityGroupings,
ETHNICITY as Ethnicity,
if(ETHNICITY='White','White',if(ETHNICITY='White - Scottish','White','BME')) as EthnicityGroupings,
GENDER as Gender,
ENTRY_QUALIFICATION as EntryQualification,
POSTCODE as Postcode,
SubField(POSTCODE,' ',1) as PartialPostcode,
CONTEXTUAL as Contextual,
ENTRY_SYSTEM as AppType,
OFFER_STATUS as OfferStatus,
APPLICANT_ID&PROGRAMME&STAGE&OFFER_STATUS as Combined,
AGENT as Agent,
MapSubString('AppDateMapping',APP_DATE) as AppDate
FROM
[\\VMWQLK03\Xls\AppDataFull\*.csv]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq);
I'm not sure if I understand your task right but I think it couldn't work - from a syntax and logically point of view. Because your where-clause only filtered your second load and not the first load respectively the joined table. Further if you apply such a filter maybe by using a logic with exists() or applymap() you will remove all records of them and not just the duplicates.
This meant you need either to use a distinct load or some logic which counts the uniqueness of your records and removed those ones with an index > 1.
