On fields that contains comma seperated in name

[pljluca]

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 info@pljsoftware.com $
@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 info@pljsoftware.com $
@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

[RobWunderlich]

Thanks for so clearly defining the problem and coding a well documented solution. I'd like to propose a slightly simpler solution. Instead of modifying the field list, we can use Previous(Hash256($(_vGrouping)) for the test. The Hash256 function will take the list as is, without modification.

[pljluca]

Hi Rob, ok for your simpler solution but where you want to use Previous(Hash256($(_vGrouping))? If you create an Hash string how to can you use it? How can you get the fields names?

Thanks a lot Luca Jonathan Panetta

[RobWunderlich]

I'm using the hash string as a value to compare the previous() line grouping fields with the current line values. The hash() function is just a convenient way to process a comma separated list of fields.