Create an extra column (measure) after binary load

[Ray123]

Hi Community, 

I would like to create an extra column to my data model, that is coming via a Binary Load. 

My script is like this:

        Binary [lib://AA- AA/AAA.qvw];

        //After this I want to do something 'simple' like: 

       Sum(column_sales) as New_Column 

How can I do that after a binary load?

Many thanks!

[QFabian]

Hi @Ray123 , after binary load is completed, you can add your new script, to load from other sources, or to load from a existing table from the binary load.

So, you script could be something like this :

 Binary [lib://AA- AA/AAA.qvw];

//      Let's suppose that the AAA.QVW have a couple of tables and one on them is called 'Sales'

Sales2:

Load

*,       

Sum(column_sales) as New_Column

Resident Sales;

drop table Sales;

[Ray123]

Thanks a lot @QFabian !

I tried your solution, according to the data model viewer my table is called "Fact". 

Trying your code is leading to an error: 

Fact2:
Load
*,
Sum(column_sales) as New_Column

Resident Fact;

drop table Fact;

The following error occurred:

Invalid expression

Looks like the error is in: Resident Fact

So, I think the issue is that the previous (binary load) is not recognized as loaded.... while it is at the top of my Data load editor script. 

Any ideas on this :-)? 

[Chanty4u]

Try with preceding load like:

Binary [lib://AA- AA/AAA.qvw];

Rename table FACTS to FACTS_temp;

FACTS:
Load
*,
Sum(column_sales) as New_Column

Resident FACTS_temp;

drop table FACTS_temp;

[ArnadoSandoval · Accepted solution]

Hi @Ray123 

@QFabian missed the little detail that you want to add an Aggregated column, e.g. Sum(), so it needs a Group By, my binary load script looks like the one below:

Binary [lib://DataFiles/01.Alternate States Sandbox.qvf];

NoConcatenate
TempSales:
Load *,
     Sales As New_Sales
Resident Sales;

Drop Table Sales;
Rename Table TempSales to Sales;

Exit Script;

It adds the column New_Sales, it is not a summary (Sum(Sales)) if I want to add a summary column then I need to include the Group By with all the columns in your Fact table (Sales in my case); this could be simplified by knowing more about your data,  I will not entertain that code here, unless you want it, in such a case, it will be good if you share with us, all the columns of your Fact table.

This is the Data Model of the application used for this reply.

Hope this helps,

[Ray123]

Thanks all a lot for your input! It really helped!