topic Re: Dropping FieldName with no data in QlikView

Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 17:16:03 GMT

Hi Experts -

Here is my scenario. I am concatenating different databases (with mostly different field names) and then looping through them to create different qvds for each of them by using a flag. Now the problem is that I am getting the field names from all the data bases, although with 0 values and I was wondering if there was a way to remove the extra fields before I store it in the qvd.

Here is a sample script:

Table1:

LOAD *,

1 as Flag

Inline [

Dim, Value1

A, 1

];

Concatenate (Table1)

LOAD *,

2 as Flag

Inline [

Dim, Value2

A, 10

];

FOR i = 1 to 2

TEMP:

NoConcatenate

LOAD *

Resident Table1

Where Flag = $(i);

STORE TEMP into Table_$(i).qvd (qvd);

DROP Table TEMP;

NEXT

DROP Table Table1;

The 1st qvd will include Value2 and 2nd qvd includes Value1 (completely blank) is there anyway I can drop them from the qvd?

Thanks for any ideas and suggestions.

Best,

Sunny

Re: Dropping FieldName with no data

swuehl — Mon, 25 Jan 2016 17:35:35 GMT

This is not really a solution to your request, but why not just load the tables non-concatenated and store them into QVDs, one after the other?

Re: Dropping FieldName with no data

Gysbert_Wassenaar — Mon, 25 Jan 2016 17:46:21 GMT

You can use the FieldValueCount function to check if a field contains values. Something like this maybe:

For i =0 to NoOfTables() -1

LET vTable = TableName($(i));

LET vFields = NoOfFields('$(vTable)');

For j=1 to $(vFields)

LET vField = FieldName($(j),'$(vTable)');

LET vCount = FieldValueCount('$(vField)');

WHEN $(vCount)=0 DROP FIELD $(vField) FROM $(vTable);

Re: Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 17:56:55 GMT

Gysbert,

I am a little unsure of where would this go in my script that I have shared above. Would you be able to point out?

Re: Dropping FieldName with no data

rwunderlich — Mon, 25 Jan 2016 17:58:41 GMT

The STORE statement allows for a field list. If you want only specific fields, it may be best to build a list of fields and use that in the STORE.

STORE F1, F2, F3 INTO myqvd.qvd;

-Rob

Re: Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 17:58:51 GMT

I am going through an exclusion process where two fields are common between all the databases. If I don't concatenate then I will have to loop through the exclusion process which doesn't seem like a great idea because I will be doing multiple resident loads multiple times.

Re: Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 18:01:32 GMT

Thanks for your response.

But this is just a simplification of actual scenario. In my true scenario I have 25+ databases and each of them can have 10-50 fields. Question is how would dynamically tell the STORE statement to only store those field names which were present in the database to begin with?

Best,

Sunny

Re: Dropping FieldName with no data

rwunderlich — Mon, 25 Jan 2016 18:10:00 GMT

I was suggesting you do something like Gysbert was suggesting. But rather than DROPing fields, build a STORE list.

-Rob

Re: Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 18:13:33 GMT

Rob -

Just not sure where in the script will this fit. Would you or Gysbert suggest where might it fit in here:

Table1:

LOAD *,

1 as Flag

Inline [

Dim, Value1

A, 1

];

Concatenate (Table1)

LOAD *,

2 as Flag

Inline [

Dim, Value2

A, 10

];

FOR i = 1 to 2

TEMP:

NoConcatenate

LOAD *

Resident Table1

Where Flag = $(i);

STORE TEMP into Table_$(i).qvd (qvd);

DROP Table TEMP;

NEXT

DROP Table Table1;

I am attaching the qvw, if it can make it convenient.

Thanks and regards

Sunny

Re: Dropping FieldName with no data

swuehl — Mon, 25 Jan 2016 18:16:14 GMT

If you want to follow these approaches, I think you need to QUALIFY your TEMP tables, because the FieldValueCount() of each field that originates also in Table1 would probably be larger than zero.

Re: Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 18:19:18 GMT

It seems to me that there won't be a straight forward way to do this . I guess I will have to live with those extra fields. Thanks for the advice and suggestions guys. As always all of you rock.

Best,

Sunny

Re: Dropping FieldName with no data

swuehl — Mon, 25 Jan 2016 18:23:48 GMT

How do you perform your exclusion process, using EXISTS()?

Re: Dropping FieldName with no data

Anonymous — Mon, 25 Jan 2016 18:24:24 GMT

Could you share what this exclusion process does ?

Re: Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 18:31:06 GMT

Yes, there are rules to exclude, include and tbd a single rows based on multiple different criteria on two different fields.

Re: Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 18:32:27 GMT

See my response to Stefan below. Will that help?

Re: Dropping FieldName with no data

Gysbert_Wassenaar — Mon, 25 Jan 2016 18:35:21 GMT

Create the qvd's first so you can leave out the fields you don't want and drop the tables immediately afterwards. Then create the concatenated tables from the qvd's you created.

Re: Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 18:42:40 GMT

I am not sure I follow you Gysbert

Re: Dropping FieldName with no data

Gysbert_Wassenaar — Mon, 25 Jan 2016 18:46:18 GMT

This:

Table1:

LOAD *,

1 as Flag

Inline [

Dim, Value1

A, 1

];

STORE Table1 into Table1.qvd (qvd);

DROP TABLE Table1;

Table2

LOAD *,

2 as Flag

Inline [

Dim, Value2

A, 10

];

STORE Table2 into Table2.qvd (qvd);

DROP TABLE Table2;

Table1:

LOAD * FROM Table1.qvd (qvd);

CONCATENATE(Table1)

LOAD * FROM Table2.qvd (qvd);

Re: Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 18:48:36 GMT

Gysbert -

The ultimate goal is not to concatenate them. The goal is to flush them into different QVDs which is then getting pulled into their respective application.

Best,

Sunny

Re: Dropping FieldName with no data

sunny_talwar — Mon, 25 Jan 2016 18:51:19 GMT

Thanks for your support guys. I know that I was hoping to get an answers without showing the complete picture. But my thinking was that it might be a very straight forward (feature or function) thing which I am not aware about.

In the interest of all of your time here, I won't ask for any more help until I get to prepare a better sample which partially shows my exclusion process. But this may take some time.

Best,

Sunny