Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all.
I have a table where some of fields may contain no data. I want to be sure that in this case field value will be Null.
Normally the solution looks like this:
table:
Load
If(Len(Trim([Field 1]))=0, Null(), [Field 1]) as [Field 1],
If(Len(Trim([Field 2]))=0, Null(), [Field 2]) as [Field 2],
If(Len(Trim([Field 3]))=0, Null(), [Field 3]) as [Field 3],
If(Len(Trim([Field 4]))=0, Null(), [Field 4]) as [Field 4]
From table.qvd(qvd);
I tried to proceed each field values using loop but have no luck. Could you, please , give some advice regarding the solution?
I suppose the solution should be something like this:
Load *
From Table.qvd (qvd);
For i = 1 to NoOfFields('Table')
Let vFieldName = FieldName($(i), 'Table');
Let vFieldValues = FieldValue($(vFieldName), 1);
If
(
Len(Trim($(vFieldValues))) = 0,
Null(),
$(vFieldValues)
)
Next i
I see. Try this then
Let vCount =NoOfFields('Table');
set vString = '';
FOR i=1 to $(vCount)
let vFieldName = FieldName(i ,'Table');
if i = 1 then
set vString = If(Len(Trim($(vFieldName)))=0, Null(), $(vFieldName)) as $(vFieldName) ;
else
set vString = $(vString), If(Len(Trim($(vFieldName)))=0, Null(), $(vFieldName)) as $(vFieldName) ;
end if
Next
Table2:
NoConcatenate load
$(vString)
Resident Table;
drop table Table;
Why don't you just apply a different null interpreter?
SET NullInterpret = '';
NULLASVALUE *; //list all fields you want to check this on or * for all
NULLASNULL *; //switch null as value off in case you don't want it applied to the subsequent tables
Hi Lorenzo.
This function unfortunately don't work for me. For some reason, some of cells that contains no data didn't turns into clear Null . They are still stays empty and turning into Null only after If condition, that I described in the example upper.
I see. Try this then
Let vCount =NoOfFields('Table');
set vString = '';
FOR i=1 to $(vCount)
let vFieldName = FieldName(i ,'Table');
if i = 1 then
set vString = If(Len(Trim($(vFieldName)))=0, Null(), $(vFieldName)) as $(vFieldName) ;
else
set vString = $(vString), If(Len(Trim($(vFieldName)))=0, Null(), $(vFieldName)) as $(vFieldName) ;
end if
Next
Table2:
NoConcatenate load
$(vString)
Resident Table;
drop table Table;
Prefect solution! Thank you, Lorenzo!