Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)' ?
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;
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?
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.
Thank you,
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).