Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jleberre
New Contributor

generic database

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;

Tags (1)