Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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,
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;
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;
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 :-)?
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;
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,
Thanks all a lot for your input! It really helped!