Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Loading problem

Hi all,

I am facing a loading problem, I have three tables: sales, purchase and store and I need to use all of them in a single qvw file.

Now, sales and purchase are indexed by documentID and article, and I have the date; Store is indexed by month-year and article.

Does anyone knows if a loading pattern exists about this problem? Or, how can I "merge" these files?

Thanks in advance

1 Solution

Accepted Solutions
MVP
MVP

Re: Loading problem

Hi Carlo,

QlikView links tables when one or more fields are named alike. Note that QlikView is case sensitive, so "Field" is not the same as "FIELD". This happens regardless the actual key fields or indexes in the data source.

You can append one table to any previously loaded table using the keyword CONCATENATE before the load, so both tables result in one bigger table.

Sales:

LOAD DocID,

     Item,

     Date,

     'Sales' AS Source;

SQL SELECT *

FROM Sales;

CONCATENATE (Sales) LOAD DocID,

     Item,

     Date,

     'Purchase' AS Source;

SQL SELECT *

FROM Purchases;

Stores:

LOAD StoreNo,

     Item,

     'Store' AS Source;

SQL SELECT *

FROM Stores;

Then create a table for the calendar where all possible date related dimensions are stored as in this application.

Hope that helps.

Miguel

2 Replies
MVP
MVP

Re: Loading problem

Hi Carlo,

QlikView links tables when one or more fields are named alike. Note that QlikView is case sensitive, so "Field" is not the same as "FIELD". This happens regardless the actual key fields or indexes in the data source.

You can append one table to any previously loaded table using the keyword CONCATENATE before the load, so both tables result in one bigger table.

Sales:

LOAD DocID,

     Item,

     Date,

     'Sales' AS Source;

SQL SELECT *

FROM Sales;

CONCATENATE (Sales) LOAD DocID,

     Item,

     Date,

     'Purchase' AS Source;

SQL SELECT *

FROM Purchases;

Stores:

LOAD StoreNo,

     Item,

     'Store' AS Source;

SQL SELECT *

FROM Stores;

Then create a table for the calendar where all possible date related dimensions are stored as in this application.

Hope that helps.

Miguel

Not applicable

Loading problem

Hi Carlo,

without knowing the Data, it cannot be merged. I can propose a Datamodel based on your explanation.

Sales:

Load

     documentID &'_' &article as Key,

     documentID as DocId, // renaming so that no synthetic key arises

     article as Material // renaming so that no synthetic key arises

From Sales;

Purchase:

Load

     documentID &'_' &article as Key,

     documentID,

     article

From Purchase;

Now the next step..I dont know when you meant I have a date, whether it is in Purchase or Sales Table


Store:

Load article,

     Month-year

From Store;

Hope it gives a basic Idea. If data is present in purchase table, you can make a Key between Article and date, like I made between DocumentID and article in first table.


Regards

Sravan

Community Browser