Loading from the same table several times - and suddenly ...

[datanibbler]

Hi,

I have the following interesting problem:

I load from a set of Excel tables (currently five) in a directory using a FOR EACH loop.

Now, in one loop I load all five Excel tables in sequence and I have a preceding load with a clause like >> WHERE ISNUM(ID) <<
  => I want to make sure that I load only those records where there is an ID so that I can associate the records with some data
        that I have loaded earlier.

Then comes a second loop over those same five tables that is actually identical BUT for the preceding load
  => The clause is exactly turned around, it's like >> WHERE NOT(ISNUM(ID)) <<
  => Here I want to load just those records that have no ID, but something like >> n.z. << (abbreviation for "not assignable") so I
       cannot associate those costs with anything (books or some other generic-use materials)

=> My problem now is, in that second loop, I get an error message stating that some field can't be found. Now I'm just about to rebuild
      the entire LOAD statement to find out why that could be, but I can actually not make head or tail of it because I have just loaded data
      from those same tables and the LOAD statement is exactly the same - and the first one worked fine ...

Can anyone give me a hint where I should look for an error (except just reading the LOAD 1.000 times and hoping that at the 1.000th time I will notice something)?`

Thanks a lot!

Best regards,

DataNibbler

[avinashelite]

Hi DataNibbler,

A few Questions/ taught:

*In the Where clause why your not using the Exist function , I feel using that will be more elegant.

*In the second load your using a Resident load from first table or your loading the file again?? if its not  Resident load then we might need to check on structure of data.

*If you share the script that do give us a better understanding , to check what's happening

[datanibbler · Accepted solution]

Hi,

I think I have it now. As I said, when looking at it for the 1.000th time I suddenly noticed that I had written the wrong worksheet_name in the LOAD (i had copied it from an earlier one).

@ Avinash

A RESIDENT LOAD would not have worked in that case - I had filtered out certain records in the first loop, and in the second one I wanted exactly those, so a RESIDENT would obviously not give me what I wanted.

I don't quite understand what you mean about an EXISTS clause, but yes, I guess I could have used that as well. There are many ways leading to Rome ...

[avinashelite]

Woo Cool !!!! small mistake