Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Ray123
Contributor II
Contributor II

Create an extra column (measure) after binary load

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!

 

 

Labels (3)
1 Solution

Accepted Solutions
ArnadoSandoval
Specialist II
Specialist II

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.

Binary-Load-01.png

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

5 Replies
QFabian
Specialist II
Specialist II

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;

QFabian
Ray123
Contributor II
Contributor II
Author

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
MVP
MVP

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
Specialist II
Specialist II

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.

Binary-Load-01.png

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

Ray123
Contributor II
Contributor II
Author

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