Discussion Board for collaboration related to Creating Analytics for QlikView.
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
cf_$(I) as cf_$(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)' ?
LOAD * INLINE [
LOAD 0 as cf
for a=1 to 9
load 'cf_$(a)' as cf
Inner Join (test)
ExistingCfs as cf
DROP Table ExistingCfs;
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
code as codice,
name as cognome,
surname as nome,
date(time_acc) as data,
applymap('Lingua', account) as azienda,
you see, I have to manipulate some fileds except cf fields that are time-varying.
I thought you could do something like this:
applymap('Lingua', account) as azienda
code as codice,
#### --> I don't know how I can insert here cf fields whithout writing them explicitly
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:
Have I explained better?
for a=1 to 999
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.
unfortunately it gave me an error: field not found cf_0.
How can I do?
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).