Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Setting up Data Model using QVD and incremental loading

Hi, I'm in the beginning stage of developing my first QV dashboard. I have one years data and it is of very large size. The data is basically of tickets resolved every month and the one's still open currently. There are four tables (TicketDetails, CustomerDetails, AssigneeDetails & Category) and my idea is to save a QVD containing all these tables for all the resolved tickets month-wise(as they remain static), then have another set of same four tables for all the open tickets and the new ones that are submitted (incremental loading). I'm banking on auto-number for using as KEY.

Can someone please help me on how to go about setting this data model as I have seen multiple blogs on issues with auto-number and QVD combination? (I'm a complete newbie) if there are any better ways to model this, your suggestions would be highly appreciated.

Waiting with anticipation.

Best Regards,

Tony

6 Replies
Highlighted

Hi

I recommend that you look at concatenating the resolved tickets and open tickets into the same table, and add a status field during load containing either 'open' or 'resolved'. The QVDs can remain as separate files, as that may simplify the management of that information.

Be careful with autonumber and incremental loading. The autonumber is unique for that reload run only - you cannot store the key in the QVD and then load the QVD and use autonumber to create additional keys - they will not be unique.

And I assume that you are aware that each QVD can store a single table only.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Not applicable

Hi Jonathan,

Thanks for the quick help. So to avoid autonumber, I am planning to use a combination of ticket number and resolved date in order to create KEY.

Also honestly I wasn't aware that only one table is stored in a QVD. So essentially i will have four QVD for each month as I have four tables.

Two quick doubts:

  1. How to add a status field during load to identify "open" and "resolved". I mean the syntax, for open tickets the resolved date field will be empty.
  2. Syntax to append or add incremental data to the existing QVD.

Would be really helpful if you could give me your advice.

Best Regards,

Tony

Highlighted
Master
Master

hi tony,

Please can you share Sample data,so can help.

Regards

Neetha

Highlighted
Not applicable

Hi Neetha,

The size if data is very large, please see below screenshot of the contents of the four tables, please see the unresolved tickets have blank "Last Resolved Date" field.

Would be great if you could give your advice.

TicketDetails.jpgCustomer.jpg

Assignee.jpgCategory.jpg

Best Regards,

Tony

Highlighted

Tony

Your data is quite well-formed and you should be able to load it with little transformation.You probably do not need separate QVDs for the Open and Resolved Tickets. To set a flag would still be useful, you would use something like this when loading the ticket details:

LOAD

     ...

     If(Len([Last Resolved Date]) = 0, 'open', 'resolved') As Status,

     ...

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Not applicable

Hi Jonathan,

Thanks, setting the flag sounds good. As you know the open tickets are dynamic; that is, they will be resolved and also new tickets will be submitted. Could you please let me know how can I combine these(new incoming tickets) with my existing QVD? Some sample syntax as above would be greatly helpful.

Best Regards,

Tony