How to use Fields from QVW in Data Load Editor

[Rich5678]

Hi,

I am working with a large Binary QVW file that has about 60 Tables mapped with each other.

These tables contain about 200+ fields.

It is a Binary QVW, and the first line of code in Data Load editor is:

"Binary lib://Applications/ProdData.qvw;"

By virtue of above statement, I can see all Fields visible in "Fields" pane of Sheet panel.

However, I do not know, how I can use these fields for Aggregations etc.

For example, I have 3 fields of interest to me (in this large QVW file) namely CUSIP, Qty, Price.

Using these 3 fields, I want to create a New Column MV as equal to Qty * Price.

I want to do this Data Load Editor (and not as an Expression on a Sheet) because I have several further Calculations I need to do, using MV values.

Instead of using a QVW data source, if I were using a Flat Data file (such as a CSV), I would have simply added following lines within Data Load Editor, such as:

Table1:
LOAD
Cusip,
Qty,
Price
FROM [lib://AttachedFiles/quest_to_comm_qvw1.csv]
(txt, codepage is 28592, embedded labels, delimiter is ',', msq);

Left Join (Table1)
Load *,
Qty * Price /100 as MktVal
Resident Table1;

Question #1:

However, when I use a Binary QVW source, I don't have a Flat anchor table (like Table1 above), to which I could join my New columns.

Even though I can see the fields in Sheet Tab, there is No intellisense of Field Names on Data Load editor. Even if I find out correct field names and create a Load statement, using a Left Join Statement, it does not get appended to Fields list (after Data loading).

Question #2:

Is there any way, when I use the QVW Binary file, that I could Delete some rows when one of the columns has a certain value. For example, in a Flat file structure, I could restrict loading of certain rows, using "where" clause like below. But, in a Binary file, nothing like that works..

Table1:
LOAD
Cusip,
Qty,
Price
FROM [lib://AttachedFiles/quest_to_comm_qvw1.csv]
(txt, codepage is 28592, embedded labels, delimiter is ',', msq)
where Cusip <> 'cusip3';

I searched Community Help pages and Manual, but could not find help on this Topic.

Your help is appreciated. Thanks.

[Digvijay_Singh · Accepted solution]

Probably you should use data model viewer to identify the right table you would like to update and check the table association/keys etc. You can join new tables to existing tables loaded thru binary statement. You can confidently assume that after binary statement you got some section of script already loaded and now you may write more script statements to either load new tables or update previously loaded tables (using resident load

You can always use Where clause with resident load to restrict or limit rows from the existing tables.

Let us know if you are facing any specific error while doing your changes.

[Rich5678]

Great piece of advice..

Thanks