topic Re: Setting up Data Model using QVD and incremental loading in QlikView

Setting up Data Model using QVD and incremental loading

Tue, 27 Jan 2015 06:17:31 GMT

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

Re: Setting up Data Model using QVD and incremental loading

jonathandienst — Tue, 27 Jan 2015 06:36:41 GMT

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

Re: Setting up Data Model using QVD and incremental loading

Tue, 27 Jan 2015 10:01:39 GMT

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:

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

Re: Setting up Data Model using QVD and incremental loading

Anonymous — Tue, 27 Jan 2015 10:29:14 GMT

hi tony,

Please can you share Sample data,so can help.

Regards

Neetha

Re: Setting up Data Model using QVD and incremental loading

Tue, 27 Jan 2015 12:12:22 GMT

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.

Best Regards,

Tony

Re: Setting up Data Model using QVD and incremental loading

jonathandienst — Tue, 27 Jan 2015 13:59:06 GMT

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

Re: Setting up Data Model using QVD and incremental loading

Tue, 27 Jan 2015 14:18:59 GMT

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