Where field problem

[fvelascog72]

Hi all,

I have a question:

In this sample script:

DATA:

LOAD

  *

Inline [

Campo1, Valor1

AAA, 111

BBB,222

]

;

DATA2:

LOAD

  Campo1,

  Valor1

Resident DATA

Where Campo2 = 'AAA'

;

DROP Table DATA;

Can I put a IF condition in WHERE to let the two names (Campo1 and Campo2)?

The problem is when loading some excel files in a loop, the field name is changing.

Thank you.

[Anonymous]

Hi,

can you explain better what do you mean "some excel files in a loop, the field name is changing".

Or attach sample data?

[sunny_talwar]

Where did Campo2 came from? I do not see that in your original DATA table?? You cannot refer a field in your resident load if they are not present in your original field.

Best,

Sunny

[#4]

Hi,

Please provide some sample data for better explanation,

Thanks

[fvelascog72]

Hi,

It was a "sample".

I do loop and I load some excel like:

For Each vFile in filelist *

     DATA:

     LOAD

          Max(Floor([Creada]))          as "Max_Fecha"

     FROM [$(vFile)]

     Where NOT [Campo1] like 'Generado*';

Next vFile;

The problem is when in excel Campo1 is renamed as Campo2.

Have I do a preload of all excel renaming the field name to Campo1?

Sorry for my poor explanation.

[swuehl]

Instead of referencing the field using embedded field names, you can use the table columns, like A, B ,C or  @1, @2 ... field references (but depending on the Excel file version also).

You need to skip the row with the embedded lables then. I would suggest to use the table wizard to create a propert LOAD statement.

This will only work if your columns in the Excel may have a changing header, but same semantics (i.e. first column is always Date field etc.).

[fvelascog72]

Hi,

In this case it should be a solution, but if you have to load several fields the field references will be not clear.

I have tried with ALIAS, previous LOAD, but not working because renames after load and I need a previous rename field.

Thank you.

[Peter_Cammaert]

If you can detect the correct name of the column-to-be-used in the WHERE clause from the actual filename in $(vFile), you could set a second variable e.g. vColumn to the name of the correct column to use. For example:

For Each vFile in filelist *

    IF check-if-vFile-meets-certain-conditions THEN

      SET vColumn = Campo1;

    ELSE

      SET vColumn = Campo2;

    END IF;

    DATA:

    LOAD  $(vColumn) AS Campo1,

          Max(Floor([Creada]))          as "Max_Fecha"

    FROM [$(vFile)]

    Where NOT [$(vColumn)] like 'Generado*';

Next vFile;

[Peter_Cammaert]

And how about putting all Excel source files back in-line by renaming their columns once and for all? This is a one-time only effort, but your load scirpt will become much simpler.

Peter

[fvelascog72]

This is what I´m doing right now.

Just thinking in other solution.