-
Re: field not found & exists() function
Anand Chouhan Sep 12, 2017 4:49 AM (in response to Camille Menten)In your variable vL.FirstDateToLoad_Metrix what type of data is num or text or you can try below
[Investment Universe]:
LOAD
Num(PurgeChar([Year Month], '-')) as [%YearMonthID],
[ISIN] as [%IsinID],
Null() as [Internal Fund Type],
1 as [IsInUniverse_],
'External' as [Fund Type],
'In' as [Investment Universe],
FROM
[$(vL.ExternalFundFile)]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq)
Where
Len(Trim([ISIN])) > 0
and
Num(PurgeChar([Year Month], '-')) >= '$(vL.FirstDateToLoad_Metrix)' ;
-
Re: field not found & exists() function
Camille Menten Sep 12, 2017 4:54 AM (in response to Anand Chouhan )hello,
no this doesn't change anything. Fact is that he still doesn't find field
[* Income Treatment]
regards,
-
Re: field not found & exists() function
Bill Markham Sep 12, 2017 4:58 AM (in response to Camille Menten)Did you say earlier that the field does not exist in some of your csv files ?
That would explain why it can't find it, if it does not always exist.
-
Re: field not found & exists() function
Camille Menten Sep 12, 2017 5:00 AM (in response to Bill Markham )yes indeed. in some files the field does not exist.
when it does not exist, I want to create the field with specific value in it: 'N/A'
when it does exist, I want to load it with it the data from the csv
-
Re: field not found & exists() function
Bill Markham Sep 12, 2017 5:29 AM (in response to Camille Menten)Have a look at this thread for inspiration - It is an old QlikView thread, but Qlik scripting has not changed for years so the thread is still valid.
-
Re: field not found & exists() function
Camille Menten Sep 12, 2017 6:11 AM (in response to Bill Markham )Hello,
Yes i’ve read that too. But it’s not 100% clear to me
Camille Menten
-
Re: field not found & exists() function
Bill Markham Sep 12, 2017 6:21 AM (in response to Camille Menten)Basically it is saying to first do a Load * to get all the fields that exist and then use the FieldNumber() function to see if the field in question exists or not and use an if statement to process for exists / not exists as per your needs
-
Re: field not found & exists() function
Bill Markham Sep 12, 2017 6:51 AM (in response to Bill Markham )If you get stuck on a particular bit then feel to post your qvf and a couple of sample csv's, with & without the field.
-
Re: field not found & exists() function
Camille Menten Sep 12, 2017 7:47 AM (in response to Bill Markham )OK, thanks for this explanation. Makes it more clear. However: am I obliged to use an Inline? Don't see the added value for me.
I'm trying to add the fieldnr into my script as below. I don't get any error message anymore, however my data is not loaded (is not going to table [Investment Universe Final]:
[Investment Universe]:
LOAD
Num(PurgeChar([Year Month], '-')) as [%YearMonthID],
[ISIN] as [%IsinID],
Null() as [Internal Fund Type],
1 as [IsInUniverse_],
'External' as [Fund Type],
'In' as [Investment Universe],
FROM
[$(vL.ExternalFundFile)]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq)
Where
Len(Trim([ISIN])) > 0
and
Num(PurgeChar([Year Month], '-')) >= '$(vL.FirstDateToLoad_Metrix)'
[Investment Universe Final]:
Load * ;
LET X = FieldNumber( [* Income Treatment],[Investment Universe]);
if $(X) = 0 then
TRACE [* Income Treatment] ;
Load
'NA' as [Income Treatment];else
Load
[* Income Treatment] as [Income Treatment];end if
-
Re: field not found & exists() function
Bill Markham Sep 12, 2017 8:38 AM (in response to Camille Menten)When you are trying to create the [Investment Universe Final] table the 2 loads don't define where the data is coming from, they probably both need a line like ;
resident [Investment Universe]
But having said there could be an even easier way to do it, here is a basic example. I am guessing that with your [$(vL.ExternalFundFile)] you are actually doing a wild card load
// First autogenerate a table with 0 rows, that defines 2 fields, Field1 & Field 2
TableOne :
Load
Null() as Field1 ,
Null() as Field2
autogenerate 0
;
// Then simply concatenate the wild card load * onto this table
Concatenate ( TableOne )
LOAD
*
FROM [lib://Bill/*.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
//Because both Field1 & Field2 have been pre-created if a field is missing from a source csv it will be filled with Null's.
I used 2 basic csv files:
File1.csv containing:
Field1, Field2
A,1
B,2
C,3
File2.csv containing:
Field1
X
Y
Z
And it worked.
Try giving this simple example a go first with a couple of test csv's like above and if it works fine for you, then you can convert it to use your real data and transformations.
-
Re: field not found & exists() function
Camille Menten Sep 12, 2017 9:08 AM (in response to Bill Markham )thank you so much! i finally see the light at the end of the tunnel! it does seem to work like this!!
now fine tuning.
-
-
-
-
-
-
-
-
-