topic Re: Resident load from entire model in QlikView

Resident load from entire model

Wed, 25 Feb 2015 16:01:09 GMT

I want to consolidate the data and avoid unnecessary data in RAM. So I have loaded few tables and want to create a consolidated table using sum and count etc to later drop the soruce tables.

I am not sure how to write the load script as if I use resident then I need to provide the table name, which is actually entire data model.

Any suggestion. ?

Re: Resident load from entire model

richard_chilvers — Wed, 25 Feb 2015 16:10:59 GMT

I'm not quite sure of your requirement.

But if you have a large (resident) table and wish to reduce by grouping on certain fields with aggregated fields, you can:

LargeTable:

LOAD all your data;

GroupedTable:

LOAD grouping fields & aggregated fields RESIDENT LargeTable GROUP BY .....;

DROP TABLE Large-table;

Hope this helps.

Re: Resident load from entire model

Anonymous — Wed, 25 Feb 2015 16:38:42 GMT

Hi,

You will have to make some joins or applymaps to bring the datas in one single table and then you will be able to make sum on it.

Re: Resident load from entire model

Anonymous — Wed, 25 Feb 2015 17:04:31 GMT

Hi Zahoor,

You can implement 3 tier QVD Architechture(Extraction ,Transformation(you can make calculations) and Load Layer)

when you have huge data sets its performance wise and memory wise good.

//first qvw

ExtractionTable:

LOAD all your data;

store ExtractionTable into ExtractionTable.qvd

//second qvw

TransformationTable:

LOAD * ExtractionTable.qvd;

noconcatenate

LOAD grouping fields & aggregated fields

Resident TransformationTable

GROUP BY .....;

store TransformationTable into TransformationTable.qvd

//third qvw(Visualisation)

LoadTable:

LOAD *

From TransformationTable.qvd;

Regards

Neetha

Re: Resident load from entire model

Anonymous — Wed, 25 Feb 2015 17:06:35 GMT

The data is only from database ?

Re: Resident load from entire model

Thu, 26 Feb 2015 06:35:18 GMT

What I am looking to reduction of the model itself. I have three tables, one with sales revenue based on invoice and one sales order and one with customer.

I want to reduce to simple fact table with customer, revenue and order count. and drop the original source table.

My understanding that at the end when all tables are connected, it appear like one flat fact table in Qlikview, so just wonder how to achieve that.

Re: Resident load from entire model

Thu, 26 Feb 2015 06:49:33 GMT

In order to achieve 1 flat Fact table, you have to use JOIN or Concatenate approach. The other thing is reducing the data test, for what I have understood, you can use EXISTS functions to restrict the data based on unique or use where clause.

Let me know, if that is what is required?

Re: Resident load from entire model

richard_chilvers — Thu, 26 Feb 2015 08:17:27 GMT

It sounds like you want to have a single set of details for each customer showing their total revenue and total sales?

LOAD the customer table (hopefully this already has one record per customer?).

Next LOAD the revenue details, and GROUP BY the customer code to get a single total revenue figure for each customer. You can keep these in a separate table as long as the Customer code is identical in each table because QV will JOIN them nicely for you.

Then do a similar thing for your order table.

Good luck.

Re: Resident load from entire model

Thu, 26 Feb 2015 15:52:18 GMT

Thanks. Seems making some sense, but can you help me further by example or some pseudo code

I have loaded the customer table which is in table i.e. Customer with fields Customer Name, Customer Code

I also loaded the revenue in another table i.e. Cust_Revenue with fields Customer Code, Invoice

I also loaded the revenue in another table i.e. Cust_Order with Fields Customer Code, Order Id

I want to use make final internal table say Cust_Relationship with fields Customer Name, Total Revenue, Order Count

Re: Resident load from entire model

richard_chilvers — Thu, 26 Feb 2015 17:06:18 GMT

Hi Zahoor

I assume you will have several invoices, and several orders, for each customer code.

I suggest:

CustRelationship:

LOAD Customer Code, Customer Name (etc....) FROM customer table;

LEFT JOIN LOAD Customer_Code, SUM(Invoice) AS Total_Revenue

FROM Cust_Revenue

GROUP BY Customer_Code;

LEFT JOIN LOAD Customer_Code, COUNT("Order Id") AS Total_Orders

FROM Cust_Order

GROUP BY Customer_Code ;

QlikView recognises the same field Customer_Code in the 3 tables, and links them based on that.

You should end up with a table called CustRelationship with one record per customer, having customer details and a field for total revenue and one for number of orders.

I hope I have understood your requirement and got my script syntax correct !