topic Re: For each in QlikView

For each

Anonymous — Fri, 31 Aug 2012 08:11:47 GMT

Hello,

in a table I have cf_<num> fields that are not in series and are, for example, cf_148, cf_278, and cf_456.

Since these fields change from time to time, I would like a standard way to get them on without having to 'select *'.

I would like something like

FOR I =1 TO 999

LOAD

code,

cf_$(I) as cf_$(I)

FROM

[Table.QVD]

(qvd);

NEXT I

But I can not do this because in the table I don't have cf_1, cf_2, cf_3 etc. but (for example) only cf_148, cf_278 and cf_456.

Is there a way to do this with something like 'where exists cf_$(I)' ?

Re: For each

giakoum — Fri, 31 Aug 2012 08:39:50 GMT

ExistingCfs:

LOAD * INLINE [

ExistingCfs

cf_2

cf_3

cf_7

];

test:

LOAD 0 as cf

AutoGenerate(0);

for a=1 to 9

Concatenate (test)

load 'cf_$(a)' as cf

AutoGenerate(1);

next;

Inner Join (test)

LOAD

ExistingCfs as cf

Resident ExistingCfs;

DROP Table ExistingCfs;

Re: For each

Anonymous — Fri, 31 Aug 2012 09:02:06 GMT

Interesting, but not exactly what I want.

Let me explain: I have a table with several fields, among them are the type cf_ <num> cf_123, cf_370, cf_264 which vary from time to time.

This means that in the same table today I find cf_123, cf_370, cf_264, tomorrow it could be added cf_692.

I do not want to have to dip into the QlikView script every time you add a new field, so I want something standard without having to manually enter the new field as in the case load inline.

Now I have

Table:

load

code as codice,

name as cognome,

surname as nome,

date(time_acc) as data,

applymap('Lingua', account) as azienda,

cf_123,

cf_370,

cf_264

from [Table.qvd]

you see, I have to manipulate some fileds except cf fields that are time-varying.

I thought you could do something like this:

Table:

load

code as codice,

name as cognome,

surname as nome,

date(time_acc) as data,

applymap('Lingua', account) as azienda

from [Table.qvd]

left join

load

code as codice,

#### --> I don't know how I can insert here cf fields whithout writing them explicitly

from [Table.qvd]

What Ioannis Giakoumakis wrote can work if you write the fields explicitly, even if what I want is not to have a field that contains cf_fields, but bring out cf_fields as they are.

What I want to get is the starting table, but without having to explicitly write the cf_fields:

Table:

load

code as codice,

name as cognome,

surname as nome,

date(time_acc) as data,

applymap('Lingua', account) as azienda,

cf_*

from [Table.qvd]

Have I explained better?

Re: For each

giakoum — Fri, 31 Aug 2012 09:52:59 GMT

set fieldList=cf_0;

for a=1 to 999

SET fieldList=$(fieldList),cf_$(a);

NEXT;

LOAD

a,b,c,$(fieldList) from table etc....

This way you have all fields predefined. If the field does not exist, the script will fail. So what you need to do is get the existing field names from your database system tables and match them before trying to load.

Hope this helps a little.

Re: For each

Anonymous — Tue, 04 Sep 2012 07:35:28 GMT

Thank you,

unfortunately it gave me an error: field not found cf_0.

How can I do?

Re: For each

whiteline — Tue, 04 Sep 2012 07:46:08 GMT

As first step use Transform feature to rotate the table and load desired field names to a separate table as a field.

Then concatenate them into one string field separated by commas with concat() and group by.

Then you can assign the resulting value to a variable with fieldvalue().

Then you can use that variable to parametrize your load (as Ioannis suggested).