For each

[Anonymous]

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)' ?

[giakoum]

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;

[Anonymous]

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?

[giakoum]

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.

[Anonymous]

Thank you,

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

How can I do?

[whiteline]

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