Data Strings & Optimising Data Loads

[GDaw2022]

Hi all. 

We currently have a snapshot builder and for each evening it will give a status of a customer whether live or lapsed. At each month end we concatenate each daily load into a monthly QVD. Now, as time has gone on, this is becoming quite data heavy when bringing each monthly QVD into an app.

I would like to take the fields and create a string so that I can take what I want from each string into an app. However, when doing so a QVD would go from 200mb to 1.5gb and is repeated whether storing as a QVD or TXT file. 

Finally, I would also like to use the InMonth function to determine if a single result in a month has a status of Live then flag, and also if status is live at month end then flag.

Any help would be greatly appreciated 

[therealdees]

I'm not exactly sure what you're trying to achieve, but if I understood it right, you want to extract the values from a field and join them into a single string/variable (based on a flag loaded within the table)?

If so, you can load the table, set the flags, then loop through each row using Peek to add the values to a variable if they match your condition:

Temp_Data:

LOAD *

,If(status = 'Live', 'True', 'False') as live_flag

,If(datafield = MonthEnd(datafield) and status = 'Live', 'True', 'False') as monthend_live_flag

From x;

LET vTableLen = NoOfRows('Temp_Data');

For i = 0 to '$(vTableLen)' - 1

    If x Then

        LET vConcatString = '$(vConcatString)'&'|'&Peek('yourfieldname', $(i), 'Temp_Data')

    End If;

Next i;

[marcus_sommer]

It's not really clear what you are trying to do but strings will need more resources as numbers and by combining several fields the lengths of the string will increase as well as the number of the distinct field-values.

Beside this I'm not sure if you really need an intermediate step by extracting information from these qvd's else it might be sufficient to load the information - just CustomerID + Date and/or YYYYMM-Period - directly as associated table or for a mapping-table or for exists() checks.

Further considerations may be to store not only the mentioned daily-qvd else applying further load/store-statements for an appropriate extracted content within the origin creator-application.