create and fill in missing information

[AlejandroJuarez]

Hello, I have a complex problem that surpasses me.

attached qvd

- as step 1 I need to create the missing monthmane

- step 2, load all the original and created monthnames with a unique nomenclator, eg:

month_year/nomenclator/index/monthly_increment

Jan2020 /1/100/0

January2020/3/100/0

February2020/2/index(previous)*(monthly_increment)/5

March2021/3/(index(previous)*(monthly_increment)/10

* I need to complete the monthmane from Jan 2020 to the current date

** I need to copy all of the above in all months that do not have new records

Thanks for your help but this problem is overcoming me.

[Aasir]

// Step 1: Create Missing Month Names
TempCalendar:
LOAD
Date($(date), 'YYYY-MM') AS YearMonth
AUTOGENERATE (1)
WHILE Date($(date), 'YYYY-MM') >= '2020-01';

// Step 2: Load Original and Created Month Names with Unique Nomenclator
FinalTable:
LOAD
YearMonth,
AutoNumberHash128(YearMonth & Nomenclator & Index & MonthlyIncrement) AS UniqueID
RESIDENT YourOriginalTable;

CONCATENATE (FinalTable)
LOAD
YearMonth,
AutoNumberHash128(YearMonth & Nomenclator & Index & MonthlyIncrement) AS UniqueID
RESIDENT TempCalendar;

DROP TABLE TempCalendar; // Drop the temporary table once done

[AlejandroJuarez · Accepted solution]

Thanks for your answer, I get this error with the first part of the script

Se ha producido el siguiente error:

Unexpected token: ')', expected one of: ',', 'OPERATOR_PLUS', 'OPERATOR_MINUS', 'OPERATOR_MULTIPLICATION', 'OPERATOR_DIVISION', 'OPERATOR_STRING_CONCAT', 'like', ...

El error se ha producido aquí:

TempCalendar: LOAD Date(, 'YYYY-MM'>>>>>>)<<<<<< AS YearMonth AUTOGENERATE (1) WHILE Date(, 'YYYY-MM') >= '2020-01'