robwunderlich / qlikview-components Goto Github PK

After changing the DateFormat from the default 'M/D/YYYY' to 'D-MMM-YYYY' I noticed that the log file was not appending anymore. Perhaps not critical, but I generally use a more "international" D-MMM-YYYY date format to eliminate any D/M and M/D confusion in companies with US and non-US dashboard users.

From Qvc.qvs version 8.2.

Details:
Within SUB Qvc.Log, this is traced to the where clause that reads: "WHERE today(1) - Date#(subfield(@2,' ',2)) < $(Qvc.Log.v.KeepDays)"

Hi, The Set variable for YTD and Rolling12 (maybe the others to) don´t listen to current selections anymore. Is this a feature or a bug? When I try to select a year older then current the YTD value still shows the last year in the calendar.

I also experience that I want YTD for the current year but when I for example have a calendar with stop 2017-12-31 it shows YTD for 2017. A work around for this is to stop the calander on today date.

Regards David

Personal Edition users can't open the QvcDocumentation.qvw. Generate an alternative documentation for them.

(This issue moved from Google issue #40)

In Qvc.Icons on rows
43: '$(_qvctemp.currImgName)' AS Icon
47: '$(_qvctemp.currImgName)' AS Icon,

the field name is Icon, it should be [$(_fieldName)].

I experience the Set and set modifier not to work properly in Sense. Using the vSetYTD or vSetYTDModifier will ignore any selection done in the application.
It will make a YTD for my selection ignoring any of my date selections.

Removing the `{1<_Qvc.DummyField=$(Qvc.Calendar.v.SelectedFieldList)>}' SET did help solve the problem in my case. (Why do QVC use this set on a dummy field that does not exist?)

[_DateSerial] = {"<=$(=Max({1<_Qvc.DummyField=$(_Qvc.Calendar.v.SelectedFieldList_)>}[_DateSerial]))"},
[År] = {"$(=Max({1<_Qvc.DummyField=$(_Qvc.Calendar.v.SelectedFieldList_)>}[År]))"}

Hello,

in my work with Qvc library I have a table name with spaces and I have some errors when I use Qvc.IncrementalStore.

I checked the function and in Qvc_Incremental_Reload.qvs to the rows

287: LOAD DISTINCT [$(_vPrimaryKey)] as [_qvctemp.PK_values] Resident $(_vTablename);
299: CONCATENATE ($(_vTablename))
300: LOAD * FROM $(_vQvdFile) (qvd)
322: STORE $(_vTablename) INTO $(_vQvdFile);

lack the square brackets.

Thanks a lot for you work.
Luca Jonathan Panetta

PS: How can I join to the project?

(Moved from Google issue #46)

It would be good to have additional segment types in Qvc.SegmentedStore. Currently it supports 'MONTH' and 'WEEK'. Propose that it is enhanced also support 'YEAR', 'DAY' and 'HOUR'.

I had previously been using CalendarFromField on two separate fields, creating two separate calendars, with two sets of different prefixes for the calendar fields, which worked fine.

At a later point in time, I added a GetFieldValues call into the script before the CalendarFromField calls, on a separate field, and not using the same stem words. This ended up giving me an error when the script running gets to the CalendarFromField call, it tells me a table is not found when the script tries to drop it from a statement within the CalendarFromField call. If I comment out the two CalendarFromField calls, then the Get FieldValuescall works fine, and vice versa.

I created a blank new qvw to test this issue but cannot replicate it at this moment. Has anyone stumbled across this issue before?

I have attached a scrambled version of the qvw which would reload with either or the sets of calls commented out, but not with both of them being run, hopefully someone can find the issue. the GetFieldValues is on the main tab near bottom, and the two CalendarFromField calls are on the Calendar tab.

The scrambling will make some graphs not display properly.

Downlaod qvw here, it wouldn't let me attach: https://www.dropbox.com/s/p8xe20pyl1a6d49/MS%20Physician%20Scorecard%20-scrambled.qvw?dl=0

The DbExtract routine is missing a few bits to support update type Revision:

LET _qvctemp.den.updateFieldType = peek('UpdateFieldType', $(_qvctemp.den._listIndex), '$(_qvctemp.den.extractList)');

IF len('$(_qvctemp.den.updateFieldType)') > 0 THEN
SET Qvc.Loader.v.ModField.Type = $(_qvctemp.den.updateFieldType);
ENDIF

I tried to use incremental reload to load a table from Oracle DB but the where condition on datetime generate an error ORA-01843.

I have set this code:

SET Qvc.Loader.v.QvdDirectory='..\QVD\';
LET Qvc.Loader.v.BaseValue = num(MakeDate(2000,01,01));
SET Qvc.Loader.v.DatabaseDatetimeMask = 'YYYY-MM-DD hh:mm:ss';
SET Qvc.Loader.v.StoreMaxModFieldValue = -1;
SET Qvc.Loader.v.Database = ORACLE;
SET Qvc.Loader.v.ModField.Type = 'Datetime';

CALL Qvc.IncrementalSetup ('TestTable', 'REFERENCES$LASTUPDATEDATE', 'ID'); 

[$(Qvc.Loader.v.Tablename)]:        // vTablename is set by the IncrementalSetup routine.
LOAD
    *
;
SQL SELECT *
FROM TestDB."TestTable"
WHERE $(Qvc.Loader.v.IncrementalExpression);

and the Log file give me this

SQL SELECT *
FROM TestDB."TestTable"
WHERE REFERENCES$LASTUPDATEDATE >= TIMESTAMP '2000-01-01 00:00:00'
Error: ErrorSource: OraOLEDB, ErrorMsg: ORA-01843: not a valid month

I test on query in Oracle DB and the correct query is

SELECT *
FROM TestDB."TestTable"
WHERE REFERENCES$LASTUPDATEDATE >= '2000-01-01 00:00:00'

I think that the error is on row 132 in file Qvc_Incremental_Reload.qvs

LET Qvc.Loader.v.IncrementalExpression = '$(_vSqlModField) ' & $(_tempLoaderPredicate($(Qvc.Loader.v.BaseValue)));

Best Regards
Luca Jonathan Panetta

Hello Rob,

• I started to build my own ColorManagement Module as almost all suggested solutions on the community did not incorporate the idea of creating variables for independent frontend properties. But then I found the Qvc.ColorTheme() sub and I think you quite nailed it :). Though I have some questions/suggestions:

• I noticed that I receive all the color variables and not only the Qvc.Color.v.* color variables in the front end. I assume it is not the goal to have the vBlue, vDarkPurple, etc variables in the frontend as this is mentioned in the documentation '// Only ColorVariable names that begin with "Qvc.Color" will be propagated to the calling QVW.'

• I made some improvements to the ColorSample1.xlsm. The rgb or argb value and the visual color are now available on each line. This means you don't have to lookup a color manually when you are referencing variables.
  Now there is also only one input column for the user where he can input a color in 3 different ways:
  Input can be:
  -a reference to another ColorVariable
  -a RGB or ARGB text value
  -a background color
  Adding colors using the QlikView color functions is not available anymore

I added a macro and some less elegant functions (I noticed excel is not QlikView) to get it working.

I hope you find it a step forward :).

,KR Koen

ColorSample1.zip

I ran into a problem with timestamps with millisecond precision. Due to the floating point numbers of Qlik I need to add a fraction to the max timestamp retrieved from the incremental qvd to make avoid falling victim to rounding errors and loading some records twice from both the source database and the incremental qvd:

current:

• LET _vDate.Max = replace(peek('Qvc.MaxModFieldValue'), '$(DecimalSep)', '.'); // Ensure using "." as decimal sep

requested:

• LET _vDate.Max = replace(rangesum(pow(2,-26),peek('Qvc.MaxModFieldValue')), '$(DecimalSep)', '.'); // Ensure using "." as decimal sep

Hi,

Could you please list set of commands for starting list of tasks in QvMC. I am looking for some automated way of starting tasks without using Qv Management console.

Or can anyone suggest what could be the best way to achieve this using powershell scripts ?

Hi, when I try to load data on your QlikSense Example app I get the following error:

2016-10-18 21:06:09 0100 SET Qvc.Calendar.v.CreateSetVariables = -1
2016-10-18 21:06:09 0102 SET Qvc.Calendar.v.Field.Date=Date
2016-10-18 21:06:09 0103 SET Qvc.Calendar.v.Field.Day=Day
2016-10-18 21:06:09 0104 SET Qvc.Calendar.v.Field.Weekday=Weekday
2016-10-18 21:06:09 0105 SET Qvc.Calendar.v.Field.Year=Year
2016-10-18 21:06:09 0106 SET Qvc.Calendar.v.Field.Month=Month
2016-10-18 21:06:09 0107 SET Qvc.Calendar.v.Field.YearMonth=Year-Month
2016-10-18 21:06:09 0108 SET Qvc.Calendar.v.Field.Quarter=Quarter
2016-10-18 21:06:09 0109 SET Qvc.Calendar.v.Field.YearQuarter=Year-Quarter
2016-10-18 21:06:09 0112 SET Qvc.Calendar.v.ExtFields=CalendarExtFields.qvs
2016-10-18 21:06:09 0115 SET Qvc.Calendar.v.ExtSetVariables=CalendarExtSetVariables.qvs
2016-10-18 21:06:09 0117 SUB Qvc.Calendar(_startDate, _endDate, _tableName, _fieldPrefix, _firstMonth)
2016-10-18 21:06:09 No qualified path for file: "\CalendarExtSetVariables.qvs"
2016-10-18 21:06:09 Execution Failed
2016-10-18 21:06:09 Execution finished.

The script runs the first time but after that I can´t get it to load.

This is my test script causing this problem:
SET QvcLib='lib://QVC';
$(Must_Include=$(QvcLib)\Qvc_Runtime\Qvc.qvs);

SET Qvc.Calendar.v.CreateSetVariables = 1; // Create Set vars
SET Qvc.Global.Extension.Directory='$(QvcLib)/etc';
CALL Qvc.Calendar(date(Today()-100), date(Today()), 'ExtFields Calendar', 'ExtFields ');

CALL Qvc.Cleanup;

I belive it´s a simular problem that #52.

Regards
David

Hi Rob

Would you be able to provide more information on how to use the Qvc_ExpandInterval function - specifically the use of the optional 5th "step interval" parameter?

We're trying to create Hourly (or even half hourly) intervals for a set of session data and were hoping to use your function but can't seem to get it to work with the following call

Session:
LOAD * INLINE [
    SessionID, SessionStartDateTime, SessionEndDateTime, Value
    1, 01/02/2015 10:14:00, 01/02/2015 11:44:00, a
    2, 01/02/2015 09:35:00, 01/02/2015 12:00:00, b
    3, 01/02/2015 16:20:00, 01/03/2015 01:10:00, c
];

// Snapshot for illustration purposes
QUALIFY *;
SessionRaw:
LOAD * Resident Session;
UNQUALIFY *;

CALL Qvc.ExpandInterval ('Session', 'SessionStartDateTime', 'SessionEndDateTime', '', 'HOUR')

Any ideas what we're doing wrong?

Thanks

Ian

Hello,
I have a table that sometimes is empty and I want to do an incremental load, I do

// The directory for the QVD file
SET Qvc.Loader.v.QvdDirectory = '..\QVD';

// How far back to load if First Load 
LET Qvc.Loader.v.BaseValue = num(MakeDate(2000, 1, 1));

// Datetime literal format for Excel - Shipper exercise
SET Qvc.Loader.v.DatabaseDatetimeMask = 'YYYY-MM-DD hh:mm:ss'; //'MM/DD/YYYY';

// Will the max Modfield value be stored with the QVD?
// The recommended value is True. This will greatly speed up subsequent loads. 
SET Qvc.Loader.v.StoreMaxModFieldValue = -1;

/*  Database product where tables will be loaded from.
    Possible values are SQLSERVER | ORACLE | SFDC.
    Default is SQLSERVER. For non-database files, use SQLSERVER.
*/
SET Qvc.Loader.v.Database = ORACLE;
SET Qvc.Loader.v.ModField.Type = 'Datetime';

LET vs_ForceFullReload = 0;
CALL Qvc.IncrementalSetup ('MyTable', 'REFERENCES$LASTUPDATEDATE', 'PK', $(vs_ForceFullReload)); 

[$(Qvc.Loader.v.Tablename)]:
LOAD
    PK
    ,REFERENCES$LASTUPDATEDATE
;
SQL SELECT
    *
FROM MyTable
WHERE isDeleted <> 'true'
$(Qvc.Loader.v.IncrementalExpression);

but I receive an error from DB because the executed query is

SELECT
    *
FROM MyTable
WHERE isDeleted <> 'true'
and REFERENCES$LASTUPDATEDATE >= TIMESTAMP ''

I tried to solve the problem and I seen that Qvc.Loader.v.IncrementalExpression generate this problem because in Qvc_Incremental_Reload.qvs row 124

SET Qvc.Loader.v.IncrementalFloor = $(_vDate.Max);  // Save copy for user defined custom where expression

not is considered the case where QVD exists but it is empty.

I solve this problem with this code

IF (Len(Trim('$(_vDate.Max)')) = 0) then
    LET Qvc.Loader.v.IncrementalFloor = '$(Qvc.Loader.v.BaseValue)';    // Save copy for user defined custom where expression
ELSE
    LET Qvc.Loader.v.IncrementalFloor = '$(_vDate.Max)';    // Save copy for user defined custom where expression
ENDIF

// Create a where predicate
LET Qvc.Loader.v.IncrementalExpression = '$(_vSqlModField) ' & $(_tempLoaderPredicate($(Qvc.Loader.v.IncrementalFloor)));   

In the last row I replaced $(tempLoaderPredicate($(__vDate.Max))) with $(tempLoaderPredicate($(_Qvc.Loader.v.IncrementalFloor)))

Other thing, the Qvc.Loader.v.IncrementalFloor variable is "Save copy for user defined custom where expression" but in the documentation it is not present in the output funcion parameter.

1. Currently all tables get the same BaseValue when using e.g. DbExtract

What is the expected output? What do you see instead?
The desired functionality is to be able to specify table specific BaseValues in the ExtractList.

What version of Qlikview Components are you using?
8.2

On what version of Qlikview?
11.20 SR5

Please provide any additional information below.
Discussion in forum suggested that the global BaseValue can be used by default. Only to be overridden if a table specific BaseValue is provided in the ExtractList.

(Moved from Google issue 51)

Add an option per this discussion:
fe6ab7a#commitcomment-20174577

Add a Year-Quarter field to calendar. Need to ensure we give people enough heads-up to avoid conflict with any field they added.

If you have defined Qvc.Global.Extension.Directory and script fails before executing qvc.cleanup then the include of qvc.qvs failes will fail the next time you run the script.
I'm using the QVC 10.2 and Qlik Sense 3.0.3.

My test script:

$(must_include=lib://QVC/Qvc_Runtime\Qvc.qvs);
SET Qvc.Global.Extension.Directory=lib://QVC\etc\;
call qvc.calendar(today()-1000, today(), 'Test')
// call qvc.cleanup

I've identified the error to be caused within the Qvc.ColorTheme.

$(_qvctemp.colorTable):
LOAD trim(ColorVariable) as _qvctemp.ColorVariable,
     trim(ColorValue) as _qvctemp.ColorValue
FROM
[$(_qvctemp.themeFile)]
(ooxml, embedded labels, table is Sheet1)
WHERE len(trim(ColorVariable))>0 and left(trim(ColorVariable),2) <> '//'
;

If you put brackets around the table name declaration the problem dissapear.

[$(_qvctemp.colorTable)]:
LOAD trim(ColorVariable) as _qvctemp.ColorVariable,
     trim(ColorValue) as _qvctemp.ColorValue
FROM
[$(_qvctemp.themeFile)]
(ooxml, embedded labels, table is Sheet1)
WHERE len(trim(ColorVariable))>0 and left(trim(ColorVariable),2) <> '//'
;

Collection of KeyFields uses FieldValues(). This can generate incorrect results if the KeyField exists in more than one table.

In QV12.10, if the target QVD does not exist, Qvc.IncrementalSetup fails with script error:

Error: Cannot open file: xxx

In Qv12.10, the function QvdNoOfRecords(targetFile) throws a script failure if targetFile is not found.

It would be possible, using the TRACE statement, for the logging routines to write to the QlikView log file and script progress window instead of an external file. This can be helpful to see the logged text in the context of the rest of the QlikView log.

I ran into a case where I want to do incremental reloading based on a timestamp only, i.e. give me all the records newer than the max timestamp of the previous load. Several records may have the exact same timestamp. Using the timestamp as primary key as well as modification field therefore doesn't work.

I propose a conditional WHERE clause based on the existence of the primary key in the arguments passed to the UpdateQvd routine

WHEN Len('$(_vPrimaryKey)')>0 SET _qvctemp.vWHERE = WHERE NOT exists($(_qvctemp.ExistsExpr));

REM If incremental reload, load previous data and concatenate to data just read.;
IF $(_vQvdExists) THEN
// Concatenate is required if adding fields
CONCATENATE ([$(_vTablename)])
LOAD * FROM $(_vQvdFile)
$(_qvctemp.vWHERE)

Reported by dadi.sensoran, Mar 10, 2014
What is the expected output? What do you see instead?

Documentation for Qvc.IncrementalStore states that the default setting for Qvc.Loader.v.KeyFieldIsUnique is 0. However, the code sets it to -1 if no entry is provided.

What version of Qlikview Components are you using?
8.2

(Moved from Google issue #53)

Qvc.LinkTable incorrectly uses DISTINCT in LEFT JOIN to source table. This may cause rows to be deleted.

Add the developer documentation to the GitHub Wiki

In Qvc.Calendar, the code to include the extension file is suppressed for Sense. Therefore the extension mechanism is not working in Sense,

There are a handful of places where the _qvctemp. prefix has not been applied (eg. Incremental Reloads). It should be added to prevent potential collisions.

Hello,

I tried to load data from a DB table where I have a field Reference$LastUpdateDate (it is an TIMESTAMP), my DB is Oracle.
At the first loading all it's ok, all rows are loaded. After, in the second or more loading I seen that the query executed is

SELECT * FROM MyTable
WHERE REFERENCES$LASTUPDATEDATE >= TIMESTAMP '2015-01-29 00:00:00'; 

and not

WHERE REFERENCES$LASTUPDATEDATE >= TIMESTAMP '2015-01-29 18:10:54'; 

I tried to change my settings FROM

SET ThousandSep='.';
SET DecimalSep=',';

TO

SET ThousandSep=',';
SET DecimalSep='.';

and now the executed query is correctly

SELECT * FROM MyTable
WHERE REFERENCES$LASTUPDATEDATE >= TIMESTAMP '2015-01-29 18:10:54'; 

Others settings

SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;-€ #.##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';
SET DayNames='lun;mar;mer;gio;ven;sab;dom';

I think that the solution is change row 128 and 131 in Qvc_Incremental_Reload.qvs

SET Qvc.Loader.v.IncrementalFloor = $(_vDate.Max);  // Save copy for user defined custom where expression

// Create a where predicate
LET Qvc.Loader.v.IncrementalExpression = '$(_vSqlModField) ' & $(_tempLoaderPredicate($(_vDate.Max)));  

with

LET Qvc.Loader.v.IncrementalFloor = Replace('$(_vDate.Max)', '$(DecimalSep)', '.'); // Save copy for user defined custom where expression

// Create a where predicate
LET Qvc.Loader.v.IncrementalExpression = '$(_vSqlModField) ' & $(_tempLoaderPredicate($(Qvc.Loader.v.IncrementalFloor)));   

Best regards
Luca Jonathan Panetta

Reported by dadi.sensoran, Mar 7, 2014
What steps will reproduce the problem?

1. Use Qvc.DbExtract for a table with extract mode=D. Input required fields into ExtractList. Note that KeyField is a required input since mode is D. For this test, enter the KeyField with a deliberate typo in the name so that it does not specify any field that exists in the source DB table.
2. Delete any existing QVD so that the first reload is a full reload.
3. After the first reload, do another reload (the first incremental reload)

What is the expected output? What do you see instead?
The expected output is to get some warning or error on the first reload that a KeyField was specified that does not match any field in the source DB. Instead an error pops up on the second reload, i.e. the first incremental reload.

What version of Qlikview Components are you using?
8.2
On what version of Qlikview?
11.20 SR5 x64

(Moved from Google issue #52)

CALL Qvc.GetFieldValues('vFieldValue', 'DecValue', 'data space');

will throw a script error.

More information about the issue can be found here:
https://community.qlik.com/message/1105121#1105121

I suggest that Qvc.Global.Extension.Directory should be the default path for Qvc.ColorTheme.
/Vegar

Putting everything into one file would simplify deployment and make Localization more uniform with the rest of the API.

CALL Qvc.Localize('ES');

This would also be a good candidate for making extensible as well.

Thoughts?

Rob,

Excellent toolkit, thank you!
This isn't an "issue" per se...
I added new Set Variable definitions for PITR requirements we have here and would like to share them with you. The expressions include:

$(_fieldPrefix)vSetPreviousFULLMonth = Returns the entire previous month.
$(_fieldPrefix)vSetPreviousFULLMonthN = Returns the entire month "N" months back - this is parameterized.
$(_fieldPrefix)vSetRollingN - Parameterized, similar to your vSetRolling12, but with the parameter can give rolling 3 months, 6 months, 4 months, etc.

I also made a slight change to Qvc.Calendar - basically I use Dual() for the month, which allows a list box of months to be in the proper order for fiscal or calendar years.

How would you like me to submit these changes to you for your review?

Thanks again for an excellent toolkit!

The SegmentedStore routine could be integrated with some of the other QVD related routines such as IncrementalUpdate and UpdateQVD.

Add new routine(s) to create AsOfTables

(Moved from Google issue #43)

In Qvc.ExpandInterval I found a bug when I pass my fields name that contains a comma in name.

I wrote this procedure _Qvc.BuildListSeparatedFields to solve it.
I tested it with this code found in you example

LOAD 
    Rates2Date
    , Rates2Rate
    , Rates2Currency
    , Rates2Discount
    , Rates2Bank as [Rates,2 Bank] //First test
//  , Rates2Bank as [Rates,2,Bank] //Second test
INLINE [
Rates2Date, Rates2Rate, Rates2Currency, Rates2Discount, Rates2Bank
01/15/2012, 1.40, EUR, 0, CITI
01/15/2012, 1.41, EUR, 1, HSBC
01/27/2012, 1.42, EUR, .2, CITI
01/27/2012, 1.49, EUR, .2, HSBC
01/20/2012, 0.13, INR, .3, HSBC
01/28/2012, 0.14, INR, .3, HSBC
02/05/2012, 0.15, INR, .5, HSBC
03/24/2012, 1.48, EUR, .8, CITI
05/17/2012, 1.52, EUR, .2, HSBC
];

CALL Qvc.ExpandInterval ('Rates2', 'Rates2Date', '', 'Rates2Currency, [Rates,2 Bank]'); //First test
//CALL Qvc.ExpandInterval ('Rates2', 'Rates2Date', '', 'Rates2Currency, [Rates,2, Bank]'); //Second test

In Qvc_ExpandInterval.qvs at row 51 you have to replace

LET _qvctemp.er.groupingList = 
    replace('$(_vGrouping)'
        ,','
        ,' &' & chr(39) & '|' & chr(39) & '& ' 
    );

with

CALL _Qvc.BuildListSeparatedFields('_ret', '$(_vGrouping)');
LET _qvctemp.er.groupingList = '$(_ret)';

/**
@version $Id: Qvc_PurgeString.qvs 269 2014-02-02 18:00:00Z [email protected] $
@author Ing. Jr. Luca Jonathan Panetta
Purge from a string the first occurrence of specified substring.

@param 1 String. Variable name in which to return value.
@param 2 String. Source string to purge.
@param 3 String. String to erase from source string.

@syntax CALL _Qvc.PurgeString('_retvar', 'The string that you want to purge', 'string that');
*/
SUB _Qvc.PurgeString (_retvar, _sourceString, _purgeString)
    LET $(_retvar) = Left('$(_sourceString)', Index('$(_sourceString)', '$(_purgeString)') - 1) & Mid('$(_sourceString)', Index('$(_sourceString)', '$(_purgeString)') + Len('$(_purgeString)'));
ENDSUB

/**
@version $Id: Qvc_BuildListSeparatedFields.qvs 269 2014-02-02 18:00:00Z [email protected] $
@author Ing. Jr. Luca Jonathan Panetta
Return a list of parameter where each field name is between [ ] and each field is separated by & '|' &.

@param 1 String. Variable name in which to return value.
@param 2 String. Comma seperated list of fields. Note that any fieldname containing spaces should be enclosed in square brackets.

@syntax CALL _Qvc.BuildListSeparatedFields('_retvar', 'GroupField1, GroupFieldn,...'); 
*/
SUB _Qvc.BuildListSeparatedFields (_retvar, _vGrouping)
    SET _fields =;
    SET _fieldName =;
    SET _separator = ' &' & chr(39) & '|' & chr(39) & '& ';

    /* Add each field between [ ] to variable _fields. */
    DO
        LET _fieldName = TextBetween('$(_vGrouping)', '[', ']');
        IF (Len('$(_fieldName)') = 0) then
            EXIT Do
        ENDIF

        CALL _Qvc.PurgeString('_vGrouping', '$(_vGrouping)', '[$(_fieldName)]');
        LET _fields = _fields & $(_separator) & '[$(_fieldName)]';
    LOOP

    /* Add each field not between [ ] to variable _fields. */
    FOR _commaIndex = 1 to SubStringCount('$(_vGrouping)', ',') + 1
        LET _fieldName = Trim(SubField('$(_vGrouping)', ',', $(_commaIndex)));
        IF (Len('$(_fieldName)') > 0) then
            LET _fields = _fields & $(_separator) & '[$(_fieldName)]';
        ENDIF
    NEXT _commaIndex

    LET $(_retvar) = Mid(_fields, 7); //delete from string the first separator

    SET _fields =;
    SET _fieldName =;
    SET _commaIndex=;
ENDSUB

Hello Rob,

After updating today a client's server to sense 3.1 the reload of apps crash on the following:

Below part of script, see last lines " No qualified path for file: "\CalendarExtFields.qvs"

Any suggestions?

Kind regards Arjen

2016-09-21 15:10:31 0472 CALL qvc.CalendarFromField ('%Date')
2016-09-21 15:10:31 0473
2016-09-21 15:10:31 0483 UNQUALIFY "qvctemp."
2016-09-21 15:10:31 0485 CALL Qvc.GetFieldValues('_vStats', '%Date')
2016-09-21 15:10:31 2289 UNQUALIFY "qvctemp."
2016-09-21 15:10:31 2291 IF len('')=0 THEN
2016-09-21 15:10:31 2292
2016-09-21 15:10:31 2292 SET _vSource = ";LOAD FieldValue('%Date', recno()) as [%Date] AUTOGENERATE FieldValueCount('%Date')"
2016-09-21 15:10:31 2293 ELSEIF index('', '.') = 0 THEN
2016-09-21 15:10:31 2294
2016-09-21 15:10:31 2301 _qvctemp.temptab:
2016-09-21 15:10:31 2302 LOAD
2016-09-21 15:10:31 2303
2016-09-21 15:10:31 2304 max([%Date])+0 as _qvctemp.maxval,
2016-09-21 15:10:31 2305 min([%Date])+0 as _qvctemp.minval
2016-09-21 15:10:31 2306
2016-09-21 15:10:31 2304 LOAD FieldValue('%Date', recno()) as [%Date] AUTOGENERATE FieldValueCount('%Date')
2016-09-21 15:10:31 2 fields found: _qvctemp.maxval, _qvctemp.minval,
2016-09-21 15:10:31 1 lines fetched
2016-09-21 15:10:31 2308 LET _vStats.Max = replace(peek('_qvctemp.maxval'), ',', '.')
2016-09-21 15:10:31 2309 LET _vStats.Min = replace(peek('_qvctemp.minval'), ',', '.')
2016-09-21 15:10:31 2310 DROP table _qvctemp.temptab
2016-09-21 15:10:31 2312 SET _vSource=
2016-09-21 15:10:31 2313 END SUB
2016-09-21 15:10:31 2314
2016-09-21 15:10:31 0489 SET _Qvc.Calendar.v.Field.Date.Override='%Date'
2016-09-21 15:10:31 0491 CALL Qvc.Calendar(_vStats.Min, _vStats.Max, '', '', '')
2016-09-21 15:10:31 0178 UNQUALIFY "qvctemp."
2016-09-21 15:10:31 0181 LET _tableName = if(len('')=0, 'MasterCalendar','')
2016-09-21 15:10:31 0183 LET _firstMonth = if(len('')=0, 1,'')
2016-09-21 15:10:31 0184 LET monthOffset = -(1-1)
2016-09-21 15:10:31 0186 ** *** *** *** *** *** *** ** ******
2016-09-21 15:10:31 0187 IF len('%Date') > 0 THEN
2016-09-21 15:10:31 0188
2016-09-21 15:10:31 0188 SET _fDate='[%Date]'
2016-09-21 15:10:31 0189 ELSE
2016-09-21 15:10:31 0190
2016-09-21 15:10:31 0192 SET _fDay='[Day]'
2016-09-21 15:10:31 0193 SET _fWeekday='[Weekday]'
2016-09-21 15:10:31 0194 SET _fYear='[Year]'
2016-09-21 15:10:31 0195 SET _fMonth='[Month]'
2016-09-21 15:10:31 0196 SET _fYearMonth='[Year-Month]'
2016-09-21 15:10:31 0197 SET _fQuarter='[Quarter]'
2016-09-21 15:10:31 0198 SET _f_MonthSerial='[_MonthSerial]'
2016-09-21 15:10:31 0199 SET _f_QuarterSerial='[_QuarterSerial]'
2016-09-21 15:10:31 0200 SET _f_WeekSerial='[_WeekSerial]'
2016-09-21 15:10:31 0201 SET _f_DateSerial='[_DateSerial]'
2016-09-21 15:10:31 0203 IF 'VIEW' = 'VIEW' OR len('')> 0 THEN
2016-09-21 15:10:31 0204
2016-09-21 15:10:31 0204 LET _qvctemp.v.IncludeExtension = replace(
2016-09-21 15:10:31 0205 '@(Include=@(Qvc.Global.Extension.Directory)@(Qvc.Calendar.v.ExtFields))'
2016-09-21 15:10:31 0206 ,'@','$')
2016-09-21 15:10:31 0207 ELSE
2016-09-21 15:10:31 0208
2016-09-21 15:10:31 0211 LET _concatenate = if(len(NoOfRows('MasterCalendar'))>0, 'CONCATENATE ([MasterCalendar])', '')
2016-09-21 15:10:31 0213 [MasterCalendar]:
2016-09-21 15:10:31 0214
2016-09-21 15:10:31 0215 LOAD
2016-09-21 15:10:31 0216 _,
2016-09-21 15:10:31 0217 dual([Year] & '-' & [Month],Date(MonthStart([%Date],-0))) as [Year-Month]
2016-09-21 15:10:31 0218
2016-09-21 15:10:31 _No qualified path for file: "\CalendarExtFields.qvs"
2016-09-21 15:10:31 Execution Failed
2016-09-21 15:10:31 Execution finished.

In order to always retain only a specific period (e.g. 2 years) worth of data in the imcremental qvd, I'd like to suggest that you have the ability to add a from date to the qvc incremental load so the qvd concatenate adds a where clause. Added as an optional parameter in incrementalstore?

Thanks

When QVC was hosted in SVN, the @Version $Id$ tag was used to track version number and source file location, In Git, version number of the source has no useful meaning, We still use the source file in the generated QvcDocumentation, Let's replace @Version with a new @source tag to identify the source file and clarify the meaning of the tag.

After execution of DbExtract some variables were found hanging around. This should clean those up:

SET _qvctemp.den.connect =;
SET _qvctemp.den.connectionFilename =;
SET _qvctemp.den.deletes =;
SET _qvctemp.den.loadStatement =;
SET _qvctemp.den.qvdName=;
SET _qvctemp.den.updateFieldType =;

When including qvc.qvs, the following error is thrown:

"$(INCLUDE= " cannot access the local file system in current script mode. Try including with LIB path

According to the help, one could specify a parameter UpdateFieldType in the table that is read in by DBExtract sub, to override global Qvc.Loader.v.ModField.Type variable.

The table value seems not to be used anywhere in the code though.

Hi,
in Qvc_Incremental_Reload.qvs I found an issue if I call Qvc.IncrementalSetup with 2nd parameter T_FEEDBACK_ELEMENT.FEEDBACK_ELEMENT_ID.

SET vs_ForceFullReload = 0;
SET Qvc.Loader.v.StoreMaxModFieldValue = -1;
SET Qvc.Loader.v.Database = ORACLE;
SET Qvc.Loader.v.ModField.Type = 'Revision';

CALL Qvc.IncrementalSetup ('Feedback_Element', 'T_FEEDBACK_ELEMENT.FEEDBACK_ELEMENT_ID', 'T_FEEDBACK_ELEMENT.FEEDBACK_ELEMENT_ID', $(vs_ForceFullReload));

[$(Qvc.Loader.v.Tablename)]:
LOAD
	FEEDBACK_ELEMENT_ID as T_FEEDBACK_ELEMENT.FEEDBACK_ELEMENT_ID
	,FEEDBACK_ID as [Feedback ID]
	,FEEDBACK_FILENAME as [Feedback Filename]
	,FEEDBACK_ELEMENT_ID as [Feedback Element Id]
;
SQL $(App.Script.Query.ToExecute)
and $(Qvc.Loader.v.IncrementalExpression)
;

CALL Qvc.IncrementalStore;

I receive this error

Field not found - <FEEDBACK_ELEMENT_ID>
JOIN ([Feedback_Element]) 
	LOAD max([FEEDBACK_ELEMENT_ID]) as Qvc.MaxModFieldValue 
	RESIDENT [Feedback_Element]

I looked in sub and I found at row 96

95: // The SQL modfield may be a qualified name like "T.LastUpdate". Remove the qualifier to get the QVD fieldname.
96: LET _vQvdModField = subfield('$(_vSqlModField)', '.', -1);	
97: SET _Qvc.Loader.v.TableModificationField = '$(_vQvdModField)';	// Save as private global var

I think the solution could be to replace row 97 with this
97: SET _Qvc.Loader.v.TableModificationField = '$(_vSqlModField)'; // Save as private global var

Thanks a lot.

Best regards
Luca Jonathan Panetta

PS: Happy new year

In Qvc.Icons on row
38: LET _qvctemp.currImgName = capitalize(subfield(subfield(_qvctemp.img, '\', -1), '.', 1));

the Capitalize function alter the file name.
I tested the function Qvc.Icons by a file name My.Test.File.Name.png and in table as value I have only My.

To solve the problem I used
LET _qvctemp.currImgName = Left(SubField(_qvctemp.img, '\', -1), Index(SubField(_qvctemp.img, '\', -1), '.', -1)-1);

or as I like, split on two expressions

LET _qvctemp.currImgName = SubField(_qvctemp.img, '\', -1);
LET _qvctemp.currImgName = Left(_qvctemp.currImgName, Index(_qvctemp.currImgName, '.', -1)-1);

Best regards
Luca

It is possible to manually write an include statement to include a qvs file from a URL such as:
$(Include=http://www.qvcomponents.com/Qvc.qvs);
This would allow for users to always be using the latest version of QVC. However it could also mean that production applications could be effected by changes in a new release.

According to the comment below the recommended value is -1, but the code set the value to 0.

// If true, a new field "Qvc.MaxModFieldValue" will be created in the QVD.
// This will greatly increase the speed of subsequent delta loads.
// True (-1) is the recommended value.
SET Qvc.Loader.v.StoreMaxModFieldValue = 0;