Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Partial Load from script

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

14 Replies
Not applicable
Author

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

Not applicable
Author

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

Thanks Miguel

Not applicable
Author

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,

QVD error.bmp

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

A

CONCATENATE (MasterTable)

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

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

CONCATENATE (MasterTable)

LOAD *

FROM

;

// Now you have both DAY 1 and DAY 2

STORE Excel INTO

;

Miguel_Angel_Baeyens

Hi,

First of all, you don't have any table in your script above called "Excel", so that's why the STORE sentence returns an error. I'd say this table is called MasterTable but certainly there is something above that first line I can't see that is concatenated to the excel file. Is this the table you want to save?

Second, a date is a numeric value, but when called with the function "Date" it returns a literal, so yor load of the Master.xls should read like this

LET vToday = Date(Today()); // before any load

LOAD .... // Your script here

FROM  

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

WHERE Date = '$(vToday)'; // WHERE condition refers to the variable with the date, single quoted

This should be closer to what you want to get since you should only get values of today in your model, and the rest those that have been stored into the QVD file.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Not applicable
Author

Hi Miguel,

I have like a 8 Tab script that makes ups the Master Table, however, I revert back to your original script and loaded as excel and changed my script load to avoid SYN keys,

As this is taking data at value today , i will run tests daily to see if this works,

Thanks again Miguel, youre very helpful in improving my understanding of this

Kind regards

A