Resident load from entire model

[#1]

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. ?    

[richard_chilvers]

Hi

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

The data is only from database ?

[#6]

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.

[#7]

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?

[richard_chilvers]

Hi

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.

[#9]

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

[richard_chilvers]

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 !