Fill a non-existent column in csv with a "None" value instead of throwing an error.

[#1]

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?

[anlonghi2]

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

[anlonghi2]

Hanshul,

the script I sent you in my previous mail works only if you load all excel files with a for ... next loop.

Andrea

[cwolf]

//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 *;

[prieper]

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

[anlonghi2]

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

[#7]

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

}

[#8]

It gives following error:

Blank field name not allowed

Concatenate(TempData)

LOAD * From [xyz*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',')

[cwolf]

that means that at least one csv has no header line or has an invalid header line.