Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
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)