Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

fvelascog72
Valued Contributor

Where field problem

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.

1 Solution

Accepted Solutions

Re: Where field problem

If you're sure that it's always the first column that you'll use in a WHERE clause, then that will work too. The only thing is that performance will not be ideal, but that isn't much of a problem if your Excel files aren't too big.

BTW In the FOR loop, add a DROP Table DATA; statement after the LET vFile... line or you may be joining all columns from all Excel files into a single DATA table. I'm assuming that the autoconcatenate that will happen on your DATA2 table is intended?

Peter

13 Replies
ggallina
Contributor III

Re: Where field problem

Hi,

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

Or attach sample data?

Re: Where field problem

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

Not applicable

Re: Where field problem

Hi,

Please provide some sample data for better explanation,

Thanks

fvelascog72
Valued Contributor

Re: Where field problem

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.


MVP
MVP

Re: Where field problem

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
Valued Contributor

Re: Where field problem

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.

Re: Where field problem

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;



Re: Where field problem

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
Valued Contributor

Re: Where field problem

This is what I´m doing right now.

Just thinking in other solution.

Community Browser