topic Re: How to load new data for 2018 in QlikView

How to load new data for 2018

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

We have created a new customer register for 2018 and I want to merge this in Qlikview with our register from 2017. There are some minor changes in the new register.

If I just load the new register from an xlsx or an txt file Qlikview freezes. If I change all the headlines in the old register by adding "2017" the load works fine but they aren't connected.

I have worked my way around this with

Directory;
LOAD [Status 2017] as [Status],

[Created By 2017] as [Created By], ....

But this means I have to do this in every single headline and this means all the fields become Keyfields and I loose the ability to use frequency in my listboxes.

I am guessing there are a better way to do this?

Can someone push me in the right direction?

Thanks

Re: How to load new data for 2018

Anonymous — Fri, 05 Jan 2018 08:13:05 GMT

Hi There,

Do you always load the customer registers from a xlsx or a qvd?

Regards,

Re: How to load new data for 2018

sloman123 — Fri, 05 Jan 2018 08:15:08 GMT

Always from xlsx

Thanks

Re: How to load new data for 2018

Anonymous — Fri, 05 Jan 2018 08:30:41 GMT

If the customer registers are in the same format, Qlikview will automatically concatenate the tables and as such form a single customer register.

If not perhaps try convert them to the same format, or Force concatenation using CONCATENATE statement, just ensure the key fields are the same in order to accurately link to the rest of your model.

Re: How to load new data for 2018

pradosh_thakur — Fri, 05 Jan 2018 08:35:57 GMT

can we have a look at your script? it will give us a clear idea where the issue is .

Re: How to load new data for 2018

qliksus — Fri, 05 Jan 2018 08:36:12 GMT

why the qlikview freezes is Cartesian product created ? I mean does the new register has a different column name but use force join the tables ?

Re: How to load new data for 2018

thomaslg_wq — Fri, 05 Jan 2018 09:40:14 GMT

Hi,

Best thing would be to not add 2017 or 2018 in fields names and keep the same name in multiple source files, just add a year column.

A workaround would be to create a specific config as :

// Parameters (fields in files and the names they should have)

Config:

LOAD * INLINE [

Year, File, FieldName, FieldRename

2017, FileName2017.xlsx, Status 2017, Status

2017, FileName2017.xlsx, Created By 2017, Created By

2018, FileName__2018.xlsx, Status-2018, Status

2018, FileName__2018.xlsx, Created-By 2018, Created By

];

// Distinct files

Files:

LOAD distinct File, Year Resident Config;

// Assuming there is only one year sufix by file

for i=0 to noofrows('Files')-1

let vAppelTable=if($(i)=0,'TableFinale:','Concatenate(TableFinale)');

let vFile=peek('File',$(i),'Files');

let vYear=peek('Year',$(i),'Files');

// Creating the script for each file

PrepaScript:

LOAD

peek('PeekRenamingField',-1)&if(rowno()>1,',')&RenamingField as PeekRenamingField;

LOAD

'['&FieldName&'] as ['&FieldRename&']' as RenamingField

resident Config

Where File='$(vFile)';

let vScript=peek('PeekRenamingField',-1,'PrepaScript');

Drop table PrepaScript;

// Loading data for this loop = this file, these fields, etc.

$(vAppelTable)

LOAD

$(vYear) as FileYear,

$(vScript)

FROM $(vFile) (ooxml, embedded labels, table is Feuil1);

next i

Drop tables Files, Config;