Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi friends,
I have an issue at hand which requires to work with multiple csv files to extract fields in qlikview script.
Some of the CSV files don't have the header field that I am statically assigning.
Eg:
MainData:
LOAD id as ID,
relative_time as REL_TIME,
absolute_time,
time(absolute_time,'hh:mm:ss') as ABS_TIME,
[A/B/C/D] as D,
[A/B/C/E] as E,
[[A/B/C/F] as F,
[[A/B/C/G] as G,
FROM
[xyz*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',');
Problem: some of the old CSVs don't have field "A/B/C/E" and "A/B/C/F" so qlikview shows an error window.
I want that qlikview fills "None" for E and F for CSV that don't have these values instead of showing Error while reloading script.
Does any of you faced this problem before?
Hi Anshul,
try with the following script
set ErrorMode=0;
MainData:
LOAD id as ID,
relative_time as REL_TIME,
absolute_time,
time(absolute_time,'hh:mm:ss') as ABS_TIME,
[A/B/C/D] as D,
[A/B/C/E] as E,
[[A/B/C/F] as F,
[[A/B/C/G] as G,
FROM
[xyz*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',');
if ScriptError=11 then
LOAD id as ID,
relative_time as REL_TIME,
absolute_time,
time(absolute_time,'hh:mm:ss') as ABS_TIME,
[A/B/C/D] as D,
'None' as E,
'None' as F,
[[A/B/C/G] as G,
FROM
[xyz*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',');
endif
Let me know
Best regards
Andrea
Hanshul,
the script I sent you in my previous mail works only if you load all excel files with a for ... next loop.
Andrea
//Create a empty table for Concatenate
MainDataTemp:
LOAD * INLINE [
id
];
Concatenate(MainDataTemp)
LOAD * FROM [xyz*.csv](txt, codepage is 1252, embedded labels, delimiter is ',');
NULLASVALUE E,F;
set NullValue='None';
MainData:
LOAD id as ID,
relative_time as REL_TIME,
absolute_time,
time(absolute_time,'hh:mm:ss') as ABS_TIME,
[A/B/C/D] as D,
[A/B/C/E] as E,
[A/B/C/F] as F,
[A/B/C/G] as G
Resident MainDataTemp;
DROP Table MainDataTemp;
NULLASNULL *;
Nice solution by Andrea.
Alternatively you may load only the first line as data and inspect, whether it has A/B/C/E and then branch into the one or the other load-script.
aircode:
inspectheader: load @1 from ....csv [text, no labels] where recno() =1;
if wildmatch(peek('@1', 0, 'inspectheader'), '*A/B/C/E*') then
load ....;
else
load ...;
end if
Dropmtable inspectheader;
HTH Peter
This script use a for loop to load all csv files.
Directory;
set ErrorMode=0;
for each vFiles in filelist('xyz*.csv')
MainData:
LOAD id as ID,
relative_time as REL_TIME,
absolute_time,
time(absolute_time,'hh:mm:ss') as ABS_TIME,
[A/B/C/D] as D,
[A/B/C/E] as E,
[A/B/C/F] as F,
[A/B/C/G] as G,
FROM
[$(vFiles)]
(txt, codepage is 1252, embedded labels, delimiter is ',');
if ScriptError=11 then
LOAD id as ID,
relative_time as REL_TIME,
absolute_time,
time(absolute_time,'hh:mm:ss') as ABS_TIME,
[A/B/C/D] as D,
'None' as E,
'None' as F,
[[A/B/C/G] as G,
FROM
[$(vFiles)]
(txt, codepage is 1252, embedded labels, delimiter is ',');
end if
next
Can we make these field dynamic some how?
OR
Can I get to know exactly which field was not present programmatically so that I can say something like
if( ! $field.isExist() ){
'None' as $field
}
It gives following error:
Blank field name not allowed
Concatenate(TempData)
LOAD * From [xyz*.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',')
that means that at least one csv has no header line or has an invalid header line.