Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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