topic Re: Deleting bogus fields from a table in QlikView

Deleting bogus fields from a table

datanibbler — Wed, 25 Nov 2015 15:04:41 GMT

Hi,

I have a table that is created in quite a complicated manner. But that is not the issue, I have the table already.

Now there are two types of fields in this table:

- For every month (so far) in the current year, there is a field with the total of operating_hrs.

- After every such field, there is a field subtracting the previous month's total from this month's total and thus calculating how many
operating_hrs were added in the course of that month.

I attach a small sample - there are only a few months there and only one record, but I think it's enough to get the picture:

- We have no data yet for Nov and Dec, so these fields are labeled 'dummy' with a number (dummy_44 and dummy_45 in this instance).

- For that reason, QlikView cannot draw the difference. The operating_hrs in Nov ("BS_Nov") are present as a field, it's hardcoded,

but the value in the first line (as in every line) is 'NN'.

I want to delete both these kinds of fields. I also attach the code I am currently using to do that.

Strangely, when i use only the first IF_clause in the LOOP, the fields 'dummy_44' and 'dummy_45' are both deleted as they should be.

<=> When I use both IF_clauses in the LOOP, the fields with the value 'NN' are all deleted as is 'dummy_44' - but 'dummy_45' remains.

Can anybody spot why that would be so?

Thanks a lot!

Best regards,

DataNibbler

Re: Deleting bogus fields from a table

effinty2112 — Wed, 25 Nov 2015 22:10:10 GMT

Hi DataNibbler,

Reverse the order you go through the fields and your code will work

//For i= 1 to NoOfFields('Extradaten')

For i= NoOfFields('Extradaten') to 1 step -1

When you delete a field you change the index of field that follow.

NoOfFields('Extradaten') changes as you drop fields. Try this:

Set vNumLoops = NoOfFields('Extradaten')

Then run your code with For i= 1 to $(vNumLoops) and you'll get an error as the script will look for a sixth field that will no longer exist.

Cheers

Re: Deleting bogus fields from a table

tamilarasu — Thu, 26 Nov 2015 02:36:08 GMT

Hi DataNibbler,

Whenever the loop drops a field, the field position (Order) has been changed in the table. So, we need to go one step back and check the replaced column name. So, I reset the i value by using i=i-1.

FOR i=1 TO NoOfFields('Extradaten')

    v_field = FieldName($(i), 'Extradaten')
  IF Left('$(v_field)', 5) = 'dummy' OR Fieldvalue('$(v_field)', 1) = 'NN' THEN
    DROP FIELD '$(v_field)' FROM Extradaten;
    i= i-1;
  END IF

NEXT i

Check the attachment and Let me know.