Hello
I'm trying to transform data from a generic database, in order to create one field per attribute (dimensions) and type of value (measures)
To do this (cf example below and in attachment):
I extract data according to the "kpi_id",
rename the "kpi_attribute" field in order to create a new field per dimension,
concatenate these different extracts,
and use the generic prefix.
The problem (screen schot in attachment):
- for the first KPI, it is OK : value is OK
- for the next ones, it is OK : values are null
Thank you for your help!
My script:
// Source
kpi_source:
NoConcatenate
load * INLINE [
kpi_id, kpi_attribute, kpi_value, month, year
Nb_customers,, 2, january, 2017
Nb_customers,, 2, february, 2017
Nb_calls, A, 3, january, 2017
Nb_calls, B, 5, january, 2017
Nb_calls, A, 2, february, 2017
Nb_calls, B, 4, february, 2017
Nb_sales, US, 10, january, 2017
Nb_sales, FR, 5, january, 2017
Nb_sales, US, 15, february, 2017
Nb_sales, FR, 8, february, 2017
];
SourceTemp:
NoConcatenate
Load Distinct
kpi_id,
kpi_attribute,
kpi_value,
month,
year
Resident kpi_source
Where kpi_id='Nb_customers';
Concatenate
Load Distinct
kpi_id,
kpi_attribute,
kpi_attribute as customer_id,
kpi_value,
month,
year
Resident kpi_source
Where kpi_id='Nb_calls';
Concatenate
Load Distinct
kpi_id,
kpi_attribute,
kpi_attribute as country_id,
kpi_value,
month,
year
Resident kpi_source
Where kpi_id='Nb_sales';
Drop table kpi_source;
Source:
NoConcatenate
Load
month,
year,
if(len(kpi_attribute)=0,'',kpi_attribute) as kpi_attribute,
if(len(customer_id)=0,'',customer_id) as customer_id,
if(len(country_id)=0,'',country_id) as country_id,
kpi_id,
kpi_value
Resident SourceTemp;
Drop Table SourceTemp;
Generic:
Generic Load
month,
year,
kpi_attribute,
customer_id,
country_id,
kpi_id,
kpi_value
Resident Source;
kpi:
NoConcatenate
Load Distinct
month,
year,
kpi_attribute,
kpi_id
Resident Source;
Drop Table Source;
FOR i = 0 to NoOfTables()
TableList:
LOAD TableName($(i)) as Tablename AUTOGENERATE 1
WHERE WildMatch(TableName($(i)), 'Generic.*');
NEXT i
FOR i = 1 to FieldValueCount('Tablename')
LET vTable = FieldValue('Tablename', $(i));
LEFT JOIN (kpi) LOAD * RESIDENT $(vTable);
DROP TABLE $(vTable);
NEXT i
Drop Tables TableList;
Exit Script;
