Automatic qvd creation

Raju_6952 — Wed, 18 Sep 2024 09:19:42 GMT

// Step 1: Load the data from CSV files

TempData:

LOAD *,

Date(Start_Timestamp, 'YYYY-MM') AS MonthField

FROM [$(vBLR_PATH)Self Serv Report/IFR/INTERACTION_FLOW_REPORT_*.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

// Step 2: Iterate over the distinct months

FOR EACH _month IN FieldValueList('MonthField')

LET vYearMonth = _month;

TRACE Processing Month: $(vYearMonth); // Add logging for debug

// Step 3: Create a new dataset for each month

MonthData:

NoConcatenate

LOAD *

RESIDENT TempData

WHERE Date(Start_Timestamp, 'YYYY-MM') = '$(vYearMonth)';

TRACE Storing Month Data for: $(vYearMonth); // Add logging for debug

// Step 4: Store the data for the current month into a QVD

STORE MonthData INTO [$(vQVD_PATH)Self Serv/IFR_$(vYearMonth)_new.qvd] (qvd);

// Step 5: Drop the temporary month data

DROP TABLE MonthData;

NEXT _month;

// Step 6: Clean up TempData

DRO

P TABLE TempData;

Will this script works

EXIT SCRIPT;

Re: Automatic qvd creation

PrashantSangle — Wed, 18 Sep 2024 10:01:14 GMT

Try below code

// Step 1: Load the data from CSV files

TempData:

LOAD Distinct

MonthStart(Start_Timestamp, 'YYYY-MM') AS MonthField

FROM [$(vBLR_PATH)Self Serv Report/IFR/INTERACTION_FLOW_REPORT_*.csv]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

// Step 2: Iterate over the distinct months
For i=0 to NoOfRows('TempData')

Let Monthfield =Peek('MonthField',$(i),'TempData');

// Step 3: Create a new dataset for each month
MonthData:
NoConcatenate
LOAD *
FROM [$(vBLR_PATH)Self Serv Report/IFR/INTERACTION_FLOW_REPORT_*.csv]
WHERE MonthStart(Start_Timestamp, 'YYYY-MM') = '$(Monthfield)';

// Step 4: Store the data for the current month into a QVD
STORE MonthData INTO [$(vQVD_PATH)Self Serv/IFR_$(Monthfield)_new.qvd] (qvd);

// Step 5: Drop the temporary month data

DROP TABLE MonthData;

Next i;

// Step 6: Clean up TempData

DROP TABLE TempData;

Regards,

Prashant Sangle

topic Re: Automatic qvd creation in App Development

Automatic qvd creation

Re: Automatic qvd creation