Adding a new column to a multiple table load

mattphillip — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Adding a new column to a multiple table load

marcus_sommer — Thu, 19 Jul 2018 14:55:50 GMT

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

topic Adding a new column to a multiple table load in QlikView

Adding a new column to a multiple table load

Re: Adding a new column to a multiple table load