Partial Load from script

[#1]

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

A

[kaushiknsolanki]

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

[#3]

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

A

[kaushiknsolanki]

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

[#5]

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

[#6]

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

[#7]

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());

[kaushiknsolanki]

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

[#9]

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

A

[Miguel_Angel_Baeyens · Accepted solution]

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