topic Partial Load from script in QlikView

Partial Load from script

Wed, 29 Jun 2011 11:47:31 GMT

Hi guys

I have a QV doc, it is fed from a spreadsheet .

When I reload my QV doc everymorning it automatically updates 5 field from this spreadsheet, Date, Time , Code, Number and Value.

Fine and no worries.

But througout the day the team uses this doc and deletes the days previous activities and updates with "todays", so tomorrow morning if I reload QV I have lost the data from the day previous.

Is there a function in the script where it will retain the field values and just "Add on" the new data ???

Please help

Thanks

Partial Load from script

kaushiknsolanki — Wed, 29 Jun 2011 11:53:52 GMT

Hi,

You can do one thing create a QVD out of it.

Update Qvd daily, with incremental load.

Then use QVD as your data source.

Regards,

Kaushik Solanki

Partial Load from script

Wed, 29 Jun 2011 13:51:51 GMT

Hi Kaushik,

Thanks for your response, but I'm not too sure what you mean ?

Can you please give me a little more detail ?

Thanks

Partial Load from script

kaushiknsolanki — Wed, 29 Jun 2011 17:18:33 GMT

Hi,

I am sorry for that.

What i mean is, you can create a qvd that will hold the same data as your excel sheet.

As you said your excel sheet gets refreshed with a new data daily and old data gets deleted.

So what you can do is you can update qvd daily and use that qvd for developing your application.

So in these way you will not loose the old data as that will be available in QVD.

If possible i will upload a sample application.

Regards,

Kaushik Solanki

Re: Partial Load from script

Thu, 30 Jun 2011 10:58:53 GMT

Hi,

You can use partial load using ADD load. Just add keyword ADD in front of LOAD and do the Partial load using Ctr+Shift+R.

Assuming you want to keep older Qvd data as it is and want to update new data from newer file.

If you use Reload, it'll overwrite QVW data.

hope this gives you answer..

Cheers

Deepak

Partial Load from script

Wed, 06 Jul 2011 09:18:45 GMT

Sorry guys

Ive been away from Qlikview for a bit , the above looks real helpful , only getting a chance to look at it now. Ill let you know how I go

Thanks

Anne

Partial Load from script

Fri, 08 Jul 2011 09:35:03 GMT

Hi Guys,

I have been testing the above and just cant it to work ,

For example, ( this is a log of things to do "TOMORROW")

Yesterday the 07/07/10, I Reloaded my data and the information fed correctly. Throughout the day ithe Excel sheets data was updated to the 08/07/10.

This morning when I Reloaded my data I lost the previous Information from the 07/07/10 and retrived the data from the 08/07/10.

However on advice from above I did change the load retrieval as a ADD Load.

I have pasted the script below, Hopefully someone can help

Thanks

CONCATENATE (MasterTable)

Add LOAD Branch,

[Deal No],

[Rate Code],

[Posting Date],

Date(Date) as Date,

Month(Date(Date)) as Month,

Week(Date(Date)) as Week,

Year(Date(Date)) as Year,

[Mat Date],

Currency,

Amount,

Rate,

Opics,

'1' as Number,

mid(Opics,18,9) as [Customer Number],

Code,

Type,

[Branch code],

ApplyMap ('branchId',[Branch code]) as [Opics Team],

ApplyMap('numm',mid(Opics,18,9),'Text Update') as [Business Unit],

/*if(if(mid(Opics,6,2)='AL','Annuity',ApplyMap('ratm',[Rate Code]))='Structured','Structured Rollover','Unstructured Rollover') as Activity,*/

if(if(mid(Opics,6,2)='AL','Annuity',ApplyMap('ratm',[Rate Code]))='Unstructured','Unstructured Rollover','Structured Rollover') as Activity,

if(ApplyMap('numm',mid(Opics,18,9),'Text Update')='SYN','30',ApplyMap ('CycleTimesM',if(mid(Opics,6,2)='AL','Annuity',ApplyMap('ratm',[Rate Code])))) as [Time to complete],

ApplyMap ('FCBranMap',mid(Opics,18,9),null()) as [Flexcube Branch],

text ((ApplyMap ('FCBranMap',mid(Opics,18,9),null()))& text(ApplyMap('numm',mid(Opics,18,9),'Text Update'))) as Lookup,

ApplyMap ('AMmAP',text ((ApplyMap ('FCBranMap',mid(Opics,18,9),null()))& text(ApplyMap('numm',mid(Opics,18,9),'Text Update'))),'UDF Investigate') as [Assistant Manager],

applyMap ('mMap',ApplyMap ('AMmAP',text ((ApplyMap ('FCBranMap',mid(Opics,18,9),null()))& text(ApplyMap('numm',mid(Opics,18,9),'UDF Investigate'))),null())) as Manager,

if (Date-[Mat Date]=0, 'Y', 'N') as [Maturing Structured]

FROM

(biff, embedded labels, table is [Refresh GRSS$])

WHERE Date=Date(Today());

Partial Load from script

kaushiknsolanki — Fri, 08 Jul 2011 10:00:27 GMT

HI,

As i said earlier, you can create a qvw file (Qlikview Application) which will do the incremental load from your excel sheet daily.

Go through the link below for Incremental Load.

http://community.qlik.com/docs/DOC-1870

Then you can use the qvd updated daily by Incremental load.

This way your qvd will have history data as well, even tough it has been deleted from the excel.

Regards,

Kaushik Solanki

Partial Load from script

Fri, 08 Jul 2011 11:36:16 GMT

Hi Kaushik

I have been looking at the Incremental Loads and down loaded the Cookbook with sample QVD. For me, it is still reloading the data as whats in the Excel file at time of script ( even on the tester/sample documentation)

I will keep working away at it and ill kerep you posted,

Thanks again

Re: Partial Load from script

Miguel_Angel_Baeyens — Fri, 08 Jul 2011 12:00:07 GMT

Hello Anne,

Another probably simpler option to keep usin normal loads but still keep all records and probably easier to maitain is to do a two step load, concatenate and store. Meaning that you always load from the QVD (data already loaded and stored), concatenating with the excel spreadsheet and storing into the same QVD (so now you have yesterday and today information in the same QVD), and so.

Excel: // Okay, this is not an actual excel with DAY 1 but let's pretend

LOAD * INLINE [

Date, Code, Value

08/07/2010, D, 400

08/07/2010, E, 500

08/07/2010, F, 600

];

// You already have a QVD storing DAY 2

// There's no need to specify CONCATENATE, both QVD and Excel have same field name and number

LOAD *

FROM Data.qvd (qvd);

// Now you have both DAY 1 and DAY 2

STORE Excel INTO Data.qvd;

Next time the script loads (day 3) you get day 3 from excel (it's ok) and you load QVD with days 1 and 2 as well (it's ok no data loss) and store whole three days in the QVD you will use tomorrow. And so on.

Now think of playing with variables to name the QVDs with dates, so you can keep track record of the last week, month or whatever in different QVDs, with no need to update the script to load them again.

Actually this gives me more control of what I'm loading and where it is stored with a bit of additional code.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Partial Load from script

Fri, 08 Jul 2011 12:15:08 GMT

Miguel

This is very helpful thanks,

Can I ask you have written an Inline table, is this where I put point to my Excel sheet that we are manually updating daily ?

Thanks
Anne

Re: Partial Load from script

Fri, 08 Jul 2011 12:20:22 GMT

Please ignor that last post , my brain literally stopped working, this seems perfect for my need and easy to use

Thanks Miguel

Partial Load from script

Mon, 11 Jul 2011 13:47:29 GMT

Hi Miguel

I think I understand what the script is to do,

It takes the data and copies it into a QVD file , then it loads everything from this QVD file. Then loads from the file rather than the original data source.

I have amended your query to fit my data but I seem to be getting 2 main problems,

1. It does not seem to be able to loctae the QVD file, I have attached Sreen shot of error message,

2. As I have been testing this for a couple of days now , with live data , it still seems to be overwriting, so It will load todays data and oversave data from the 8/07/11.

I have pasted the script below, I think it may be as I am using a concatenate load, this is as if I dont I create loads of SYN Keys , perhaps you can advise if my understanding is correct and if there is something I cn do to fix,

thanks a million

CONCATENATE (MasterTable)

LOAD Branch,