0 Replies Latest reply: May 23, 2018 9:47 AM by Julien Le Berre RSS

    generic database

    Julien Le Berre

      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;