Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
avantime
Creator II
Creator II

Load clients with no turnover

Hi all,

Right now I am loading clients with turnovers from a qvd file like:

Load

[Client ID],

[Invoice date],

[Invoice number],

Turnover,

Year([Inovice date]) as Year,

Month([Invoice date]) as Month

From (qvd);


This works ok, but I would also like to see the clients that have no turnover.

For this I figured that I should concatenate the invoices database with the clients database, but I have not succeeded in having a good result. The rest of the clients should show up with 0 as turnover for each month in which they had no turnover.

Any ideas?

Thanks a ton!

26 Replies
avantime
Creator II
Creator II
Author

This is the solution I thought about at first but it would result in a massive database. I`m sure the server would go bananas if I did this

johnw
Champion III
Champion III

Perhaps the attached. I created an extra DateKey table between the Invoices and the Customers. The DateKey table maps dates to themselves, but it maps 'All' to all dates. Then I just concatenate every customer with a DateKey of 'All' and an amount of 0. Create the pivot table, and you can select years, but it will show all customers whether they had sales that year (or any year) or not. As before, a simple expression of sum(Amount), nothing complicated but the data model.

Capture.PNG

avantime
Creator II
Creator II
Author

Hi John, thank you for your input, could you briefly explain me your method?

I see you load the clients together with the invoices. My invoices database contains only clients with at least one purchase. The clients database is separate from the invoices db.

avantime
Creator II
Creator II
Author

So I managed to solve it thanks to your advice, John.

Only problem is that I want to export the new database to qvd and I can`t figure out how to do it (check the script area). Any ideas?

johnw
Champion III
Champion III

You "can't" store two tables into one QVD, so you would store each table as its own QVD.

I guess I could stop there, but in case you don't know, or for others who stumble across this later that might not know, you actually have some options here, and what's best is somewhat a matter of QlikView data management philosophy, and what your company has settled upon as a standard for your data architecture, if any.

Storing each table as its own QVD is in line with a three-tier QVD architecture. This is a common and I think very good architecture where you have two layers of QVDs - the base extract QVDs, and the transformed QVDs. The extract QVDs are just simple loads from your real company data with minimal manipulation. I'm guessing your InvoiceDB.qvd and ClientDB.qvd are extract QVDs. Then from that you can build transformed QVDs which have been massaged into QlikView-friendly tables, data structures that might barely resemble the source databases. So storing your new tables as QVDs would be creating this transformed QVD layer. You might call them Invoice.qvd and InvoiceDateKeys.qvd, using "DB" to indicate that a QVD is a direct extract, and the absence of it to indicate a transformed QVD ready for load into user applications. Probably a different directory too. And this could be made a company standard, that you always have extract QVDs and transformed QVDs, and you only load real company data into extract QVDs, only read extract QVDs to build transformed QVDs, and only read transformed QVDs in the user applications. You might be creating a lot more for one application than you initially require, but you're set up in an organized way that supports expansion.

Another approach would be for the transformed layer to be QVWs instead of QVDs, called a three-tier mixed QVW architecture. So in your case you'd store this whole script as a ClientSales.qvw, but that wouldn't be your user application. Instead, you'd do a binary load of ClientSales.qvw to pull in the entire data model all at once. You're storing a QVW instead of two QVDs, and doing a binary load of the QVW instead of a load of each QVD.

There are other approaches, like single-tier (raw data -> user app), two-tier QVD (raw data -> QVD -> user app), and two-tier QVW (raw data -> QVW -> user app).

In practice, my company's architecture most closely resembles a two-tier QVD architecture, but that's not the rule we're following. We're following what we call "the rule of one". If data is only used in one user application, we'll typically load it directly from the data source into that user application (single-tier). But once we need it in another user application, we go back and build a QVD for it at that time, and modify the original application to load the QVD (two-tier QVD). Same with transforming data. If we only need the data transformed in one user application, we'll do the transformation in the user application (two-tier QVD). If in more than one, we'll build a transformed QVD (three-tier QVD). If all user applications need the same transformed QVD, and none need the extract QVD, we won't create an extract QVD (two-tier QVD). Maybe you could think of it as creating the minimum number of QVD tiers necessary to avoid duplicating the same script in more than one QVW. This rule is only loosely followed. For instance, if I expect some data will eventually be used in more than one place, I might go ahead and build a QVD for it, even if I only need it in one application right now. Anyway, I'm guessing that in our shop, the tables we just built would only be used in one place, and so I'd just put all this script in a user application and not store the transformed tables as QVDs.

swuehl
MVP
MVP

Just to add my 0.02€: You can store multiple tables into one QVD:

Storing a Data Model in a Single QVD | Qlikview Cookbook

You will notice that it stores some meta data and all fields combined in a single QVD.

This approach has some disadvantages, most notable probably that it performs much worse than single QVDs.

Personally & almost all the time, I follow one of the approaches John has described in detail above.

johnw
Champion III
Champion III

And that's exactly why I put "can't" in quotes.