Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
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
Hi There,
Do you always load the customer registers from a xlsx or a qvd?
Regards,
Hi
Always from xlsx
Thanks
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.
can we have a look at your script? it will give us a clear idea where the issue is .
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 ?
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;