Adding a new column to a multiple table load

[mattphillip]

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

/////////////////////////NEW CODE///////////////////////////////////////////////////////

join (Table)

LOAD * INLINE [

Combined,SeqNo

6533115(Hons) Business and Human Resource Management3Reject,DA

4555486BSc (Hons) Ethical Scripting3Reject,DA

5522422BSc (Hons) Nursing 2Reject,DA

3111165BSc (Hons) Sport 1Reject,DA

2113596LLB (Hons) Legal Studies1Reject,DA

] Where NOT WildMatch(SeqNo,'DA');

Any help would be most appreciated.

Yours,

Matt

[marcus_sommer]

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.

- Marcus