topic Re: Where field problem in QlikView

Where field problem

fvelascog72 — Mon, 29 Jun 2015 11:09:53 GMT

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.

Re: Where field problem

Anonymous — Mon, 29 Jun 2015 11:15:07 GMT

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

sunny_talwar — Mon, 29 Jun 2015 11:16:12 GMT

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

Re: Where field problem

Mon, 29 Jun 2015 11:26:23 GMT

Hi,

Please provide some sample data for better explanation,

Thanks

Re: Where field problem

fvelascog72 — Mon, 29 Jun 2015 11:26:36 GMT

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.

Re: Where field problem

swuehl — Mon, 29 Jun 2015 11:32:33 GMT

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

Re: Where field problem

fvelascog72 — Mon, 29 Jun 2015 11:43:37 GMT

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

Peter_Cammaert — Mon, 29 Jun 2015 12:00:49 GMT

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

Peter_Cammaert — Mon, 29 Jun 2015 12:04:04 GMT

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

Re: Where field problem

fvelascog72 — Mon, 29 Jun 2015 12:38:25 GMT

This is what I´m doing right now.

Just thinking in other solution.

Re: Where field problem

Peter_Cammaert — Mon, 29 Jun 2015 12:39:40 GMT

IMHO this is the best approach. The others are just workarounds.

Good luck,

Peter

Re: Where field problem

fvelascog72 — Mon, 29 Jun 2015 12:46:40 GMT

What do you think about this solution:

DATA:

LOAD

Inline [

Campo1, Valor1

AAA, 111

BBB,222

]

;

LET vFile = FieldName(1,'DATA');

DATA2:

LOAD

$(vFile) as "Campo2",

Valor1

Resident DATA

Where $(vFile) = 'AAA'

;

DROP Table DATA;

For the other script:

For Each vFile in filelist *

DATA:

LOAD

FROM [$(vFile)];

LET vFile = FieldName(1,'DATA');

DATA2:

LOAD

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

Resident DATA

Where NOT $(vFile) like 'Generado*';

Next vFile;

Need to load the objetive field in first position.

Re: Where field problem

Peter_Cammaert — Mon, 29 Jun 2015 13:07:19 GMT

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

Re: Where field problem

fvelascog72 — Tue, 30 Jun 2015 06:15:54 GMT

The excel files aren´t big.

The loop of the example is a little part of the load.

Thank you very much.