topic Re: Load a field only if exists , from csv in QlikView

Load a field only if exists , from csv

Fri, 05 Apr 2013 15:31:08 GMT

Hi,

I have a csv and I want to load some data, if some fields exists.

The problem is an error: it can't load if in else side of if clause it is used a field that don't exist.

Have an ideea how to make it works?

Thanks!

COLSIN:

FIRST 1 LOAD *

FROM

..file.csv;

Tab1:

LOAD

rowno() AS ORD,

if(FieldNumber('field 1','COLSIN')>0,[field 1]) AS [_FIELD1],

if(FieldNumber('field 2','COLSIN')>0,[field 2]) AS [_FIELD2]

FROM

..file.csv;

DROP TABLE COLSIN;

Re: Load a field only if exists , from csv

Gysbert_Wassenaar — Fri, 05 Apr 2013 16:47:52 GMT

Something like this maybe:

COLSIN:

FIRST 1 LOAD *

FROM

..file.csv;

for f = 1 to NoOfFields('COLSIN')

LET vFieldName = FieldName(f,'COLSIN');

if match('$(vFieldName)', 'MyField1','MyField2','MyField3', ..etc) then

LET vFieldList = '$(vFieldList)' &',' & '$(vFieldName)';

end if

LET vFieldList = mid('$(vFieldList)',2);

Tab1:

LOAD

rowno() AS ORD, MyFieldX, MyFieldY, ...etc, $(vFieldList)

FROM

..file.csv;

DROP TABLE COLSIN;

Set vFieldName=;

Set vFieldList=;

Re: Load a field only if exists , from csv

Fri, 05 Apr 2013 16:53:17 GMT

You're on the right track. First you need to obtain the list of fields available then construct a string of the fields that you want. Finally, load the data.

Try something like:

COLSIN:

FIRST 1 LOAD *

FROM

file.csv;

SET fields = "always_included_field1,always_included_field2,";

IF (FieldNumber('field 1','COLSIN')>0) THEN

LET fields = fields & ",[field 1] AS [_FIELD1]"

END IF

IF (FieldNumber('field 2','COLSIN')>0) THEN

LET fields = fields & ",[field 2] AS [_FIELD2]"

END IF

DROP TABLE COLSIN;

COLSIN:

LOAD $(fields)

FROM

file.csv;

Re: Load a field only if exists , from csv

Fri, 05 Apr 2013 17:06:07 GMT

HI ,

PLease try the below code and No Hard Code is required here .

Every thing can be held in automated way

COLSIN:

LOAD *

FROM

[..\TIME.xlsx]

(ooxml, embedded labels, table is Sheet3);

Let DList='';

Let g = NoOfFields('COLSIN');

For h = 1 to g

let DList = DList & chr(39)& FieldName(h,'COLSIN') & chr(39) & if(h<>g,',');

FieldList:

LOAD * Inline //Your Required field Names list

[

FieldName

Field1

Field3

];

Let v_attr_list ='';

For i=0 to NoOfRows('FieldList')-1

Let v_attr_list = v_attr_list & chr(39)& Peek('FieldName',i,'FieldList') & chr(39) & if(i<>j,',');

Let i=0;

For Each attr in $(DList)

Let index = MixMatch( '$(attr)' , $(v_attr_list) );

IF $(index)>0 then

if i=0 then

Result:

LOAD

RowNo() ,

$(attr)

Resident COLSIN;

let i=i+1;

else

Join(Result)

LOAD

RowNo(),

$(attr)

Resident COLSIN;

endif;

ENDIF;

DROP Table COLSIN;

Hope it helps you

If there is anything please let me know.

Re: Load a field only if exists , from csv

Mon, 08 Apr 2013 06:44:57 GMT

Job done,

Lot of thanks to all ! (I marked correct in cronological order because all responses were helpfull and corect)