how to delete specific column number from CSV loading?

[#1]

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

[sinanozdemir]

Hi Rakesh,

You can use NoOfFields() function:

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

Hope this helps.

[marcus_sommer · Accepted solution]

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

next

- Marcus

[#4]

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) ;

[marcus_sommer]

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

[kkkumar82]

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

[marcus_sommer]

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

next

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

- Marcus