Dropping FieldName with no data

[sunny_talwar]

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

[swuehl]

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?

[Gysbert_Wassenaar]

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);

  next

next

[sunny_talwar]

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?

[rwunderlich]

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

[sunny_talwar]

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.

[sunny_talwar]

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

[rwunderlich]

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

-Rob

[sunny_talwar]

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

[swuehl]

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.