topic Re: Is that possible to Append/Add data to QlikView Application based on user request. in QlikView

Is that possible to Append/Add data to QlikView Application based on user request.

Wed, 01 Apr 2015 23:46:13 GMT

Hi Guys,

I was trying to load only 6 months data into Memory initially because maximum users (80%) browse 6 months data but few staffs (20%) browse above 6 months data like 12 Months/18 months/24 months data weekly or monthly basis but not on daily basis.

The main purpose of doing this is to save RAM (Instead of loading 2 years data into ram load only 6 months and if someone wants then he/she should load 12/18/24 months data into memory but again on next day it will drooped from memory and will have 6 months data into memory ) space so that number of concurrent users may increase.

Initially I thought, I should make 4 documents each contains 6 month data and can provide 3 buttons like "Show 2nd 6 months data" ''Action:Open QlikView Document'' for 2nd 6 months/3rd 6 months/4th 6 months.

Main Application Recent 6 monthsjavascript:;
			2nd 6 Months Data Doc-2	3rd 6 Months Data Doc-3	4th 6 Months Data Doc-4

But this technique will not work if someone wants to aggregate 5th month and 7th month data because 5th month data will be available in 1st 6 month document (Doc-1) and 7th month data will be available in 2nd 6 month document (Doc-2) and both will be opening separately.

Then I thought ok lets go in this way:

Main Application (Doc-1) holds recent 6 months Data, Doc-2 Holds recent 12 months data, Doc-3 holds recent 18 months data, Doc-4 holds 24 months

Main Application Recent 6 months
			Recent 12 Months Data Doc-2	Recent 18 Months Data Doc-3	Recent 24 Months Data Doc-4

But here I will be loading repetitive data in Doc-2, Doc-3, Doc-4.

Next I thought, Is that possible that initially I should bring only 6 months data into memory and if someone request (Need to provide user control) then another 6/12/18 months data should bring into memory and should be appended with initial 6 months data.

Note: I don't want to give reload control to users.

Experts I need you guys help to fix and decide the best design.

Re: Is that possible to Append/Add data to QlikView Application based on user request.

rwunderlich — Thu, 02 Apr 2015 00:01:03 GMT

How about just two documents:

1 Recent 6 months,

2. All 24 months

-Rob

Re: Is that possible to Append/Add data to QlikView Application based on user request.

Thu, 02 Apr 2015 00:09:23 GMT

Rob,

Thanks for suggestion. There is any other way or possibility?

Can we do any thing using Binary Load?

Re: Is that possible to Append/Add data to QlikView Application based on user request.

sunny_talwar — Thu, 02 Apr 2015 01:16:46 GMT

I know you mentioned giving the reload is not a possibility, what about giving partial reload capability to users?

I have worked on application where users can load new data into the application (same format as the one already in the application) and append it to the existing data using partial reload. I feel if you give them the option to perform a partial reload, they can add the 6/12/18 months data as they might require it.

Best,

Sunny

Re: Is that possible to Append/Add data to QlikView Application based on user request.

Thu, 02 Apr 2015 01:21:04 GMT

Sunny,

I am not sure about it but I may give a try.

Could you please provide any sample script for above you have said?

I will trying implementing and see how it goes.

Re: Is that possible to Append/Add data to QlikView Application based on user request.

sunny_talwar — Thu, 02 Apr 2015 01:23:46 GMT

I am unable to share what I have, but I can work on getting you a sample application.

Best,

Sunny

Re: Is that possible to Append/Add data to QlikView Application based on user request.

Thu, 02 Apr 2015 01:24:00 GMT

Sunny,

Also I got another doubt,

If 2 users click on reload 6 months data, then will it be loaded 2 times and will start display duplicate records? I am not sure about it that How we are going to restrict that if data is already loaded in memory then don't load and display a message data is present in memory to browse.

Or Partial load will handle it automatically

Re: Is that possible to Append/Add data to QlikView Application based on user request.

sunny_talwar — Thu, 02 Apr 2015 01:26:41 GMT

Well so I was thinking the new excel file will only have the new data and it will get appended to the most recent data. But if that is not the case, we can still only append the new data by using a WHERE NOT EXIST clause and load only the data which is new.

Best,

Sunny

Re: Is that possible to Append/Add data to QlikView Application based on user request.

Thu, 02 Apr 2015 01:31:32 GMT

In my case, I have 2 years of data ready but to save RAM, I am trying to load only recent 6 months (80% staffs browse there data) and rest 20 % staffs weekly or monthly go browse 1 year or 2 years of data. There is no classified list of users. so thinking to go for dynamic data appending based on user selection or click.

Re: Is that possible to Append/Add data to QlikView Application based on user request.

sunny_talwar — Thu, 02 Apr 2015 02:07:54 GMT

Give this a shot. Hopefully this will be helpful.

Use Community_158574_v1 as the partial reload file. and you can perform the partial reload by specifying it on the front end by giving it the full path.

Let me know what you think.

Best,

Sunny

Re: Is that possible to Append/Add data to QlikView Application based on user request.

rwunderlich — Thu, 02 Apr 2015 20:09:47 GMT

You won't get duplicate rows. But you may get other problems such as file collisions. I don't think you really want users reloading - esp different data -- in the server environment. Everyone shares the same copy. So if someone reloads to add another 12 months, everyone sees the extra 12 months. Then if another user asks for an additional 6 months, the 12 months is overlayed and the first user is unhappy.

I would recommend creating 2 documents. The recent 6 months, which 80% use. It has a button to "see all data" that opens the 24 month doc.

So your RAM overlap is for 6 months. How much RAM is that?

-Rob

Re: Is that possible to Append/Add data to QlikView Application based on user request.

sunny_talwar — Thu, 02 Apr 2015 20:24:40 GMT

Hi Rob,

I wasn't recommending a reload on the access point itself. I don't even know if that is possible or not. What we are doing is that allowing users with developer license download a copy of the application from the server and perform a partial reload to compare new data against the old one. I understand the concern that letting a reload on the server would create version control issue, but a downloaded copy will be user specific and won't have that issue.

Best,

Sunny

Re: Is that possible to Append/Add data to QlikView Application based on user request.

rwunderlich — Fri, 03 Apr 2015 00:09:35 GMT

Now I understand. A couple of suggestions.

1. You create separate QVDs for each 6 month segment. Then have a variable in a drop down that lets the user select how may months they want. Reference the variable in the load to load the proper QVDs.

2. Binary load can only load one QVW. If the machine has enough RAM to Binary load the entire dataset, you can load Binary load the 24 month copy and use an INNER JOIN to delete the unwanted months.

-Rob

Re: Is that possible to Append/Add data to QlikView Application based on user request.

Fri, 03 Apr 2015 00:18:30 GMT

Yes Rob,

I am trying for this only. Even I am trying to Popup a message if any user has already loaded data.

LET vNow=Now();

LOAD_ON_REQUEST_CONFIG:

LOAD * INLINE [

RunDateTime, NumberOfMonths, LoadFlag

$(vNow), 12, 0

$(vNow), 18, 0

$(vNow), 24, 0

];

STORE LOAD_ON_REQUEST_CONFIG INTO $(vQVDsFolderPath)\LOAD_ON_REQUEST_CONFIG.QVD;

DROP TABLE LOAD_ON_REQUEST_CONFIG;

If any user has loaded the data It will be updating the flag to 1 and so next time if some one trying to reload it will check the flag in table if it is already 1 then display a message that data has been reloaded by other user and it is available to all.

Re: Is that possible to Append/Add data to QlikView Application based on user request.

rwunderlich — Fri, 03 Apr 2015 03:38:09 GMT

How about just creating all 4 documents on the server and letting the user download the one they want? Why reload?

Re: Is that possible to Append/Add data to QlikView Application based on user request.

sunny_talwar — Fri, 03 Apr 2015 10:27:20 GMT

Did you get time to look at what I posted? This isn't going to work for you??

Best,

Sunny

Re: Is that possible to Append/Add data to QlikView Application based on user request.

Mon, 06 Apr 2015 23:40:31 GMT

Hi Rob,

I tried this but problem is that when users want to see 5th month data and 7th month data (in one document), in that case 5th month data resides in 1st 6 month document and 7th month data resides in 2nd 6 months document.

So I am looking for the ways to combining all 4 documents data into one and show if required.

If you say it is possible could you please elaborate it.

Re: Is that possible to Append/Add data to QlikView Application based on user request.

Mon, 06 Apr 2015 23:49:03 GMT

Hi Sunny,

Based on your logic, I am trying to do Binary load from a 24 month QVW files based on a variable 12 months, 18 months, 24 months.

So I will have a QVW which holds 24 months data. In my application, I will use this qvw file to do binary load and show only 6 months data default and when users want to see 12 months or 18 months or 24 months, he/she should select a variable and the click on reload (Full Load) and then It will load 12 month or 18 months or 24 months based on variable selection and I will have QVD file which holds RunDateTime,NumberOfMonths,LoadFlag

LET vNow=Now();

LOAD_ON_REQUES_CONFIG:

LOAD * INLINE [

RunDateTime, NumberOfMonths, LoadFlag

$(vNow), 12, 0

$(vNow), 18, 0

$(vNow), 24, 0

];

Initially LoadFlag will be 0 and if any users reloaded data on that day it will set to 1 so that when next user try to reload data It will pop a message that data is already reloaded by other user and available for all.

Re: Is that possible to Append/Add data to QlikView Application based on user request.

Tue, 07 Apr 2015 09:21:15 GMT

Hi Rob,

Could you please respond If you have any suggestion on above quires?

Re: Is that possible to Append/Add data to QlikView Application based on user request.

sunny_talwar — Tue, 07 Apr 2015 11:57:35 GMT

Did it work out for you?

Best,

Sunny