topic Re: how to delete specific column number from CSV loading? in QlikView

how to delete specific column number from CSV loading?

Wed, 25 Nov 2020 16:16:04 GMT

Dear QlikView users,

I have N number of columns in a CSV and want to exclude the last 2 columns while loading.

What are the COLUMN FUNCTIONs available for Scripting to solve my issue?

Kind Regards, Rakesh

Re: how to delete specific column number from CSV loading?

sinanozdemir — Mon, 25 Apr 2016 15:03:49 GMT

Hi Rakesh,

You can use NoOfFields() function:

Let vNoOfColumns = NoOfFields('your table name');

Hope this helps.

Re: how to delete specific column number from CSV loading?

marcus_sommer — Mon, 25 Apr 2016 15:08:14 GMT

Try something like this:

for i = 1 to nooffields('YourCSV')

if $(i) >= nooffields('YourCSV') - 2 then

let vFieldname = fieldname($(i), 'YourCSV');

drop field $(vFieldname);

end if

- Marcus

Re: how to delete specific column number from CSV loading?

Mon, 25 Apr 2016 15:30:55 GMT

Thanks Marcus for quick reply!

Is there any simple method without variables; somewhat like below ...=>

LOAD *

FROM
$(vPfad_Local_Month) YourCSV
(txt, codepage is 1252, no labels, delimiter is ';', msq, header is 5 lines)

Where drop column (@XA), drop column (@XB) ;

Re: how to delete specific column number from CSV loading?

marcus_sommer — Tue, 26 Apr 2016 06:55:26 GMT

I think there is no easier way unless you relinquish to the wildcard-load and determined the to load fields explicitely. Of course there are further alternatives like reading the field-header and creating from there an automatically load-statement but such things makes no sense - either you could use a wildcard-load 1:1 as simplification or not then every needed adjustment to them creates only confusion.

- Marcus

Re: how to delete specific column number from CSV loading?

kkkumar82 — Tue, 17 May 2016 08:45:32 GMT

Hi Marcus ,

Excuse for writing in a closed thread, I got struck in the earlier logic which you proposed

-> fields are not coming in load order

-> if there are 11 fields for example even 9th field is being removed as it meets 9 >=11-2 condition

-> once a field is removed the nooffields() is giving 10 fields like wise

Can you check them

Re: how to delete specific column number from CSV loading?

marcus_sommer — Thu, 19 May 2016 07:36:49 GMT

Yes you are right - if I query the NoOfColumn after each dropping again I will drop any columns. For this the logic needs to be a bit adjusted, maybe in this way:

let vFieldCounter = nooffields('YourCSV');

for i = 1 to $(vFieldCounter)

if $(i) >= $(vFieldCounter) - 2 then

let vFieldname = fieldname($(i), 'YourCSV');

drop field $(vFieldname);

let i = $(i) - 1;

end if

It's not tested and maybe the logic had further some weaknesses but it showed in each case the needed direction.

- Marcus