Qlik Community

Qlik Sense App Development

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

Not applicable

Qlik Sense mapping load issue when selecting more than 2 field in sql select

Hi

I'm converting a script from qlikview to qlik sense.

I have an issue with a mapping load when I select more than 2 fields in my SQL select statement. This data somehow gets loaded and is not deleted after the load is done  (and will create a synth key on field2 and field3 because I use those in another table as well).

I guess this is a bug as it did work allright in qlikview, and the fields in the SQL select should not be loaded, but only the fields in the LOAD statement.

Script:

MapUsers:

Mapping LOAD

  field1,

  (field2 & ' ' & Replace(field3, '_', ' '))

  ;

SQL SELECT

field1, field2, field3

FROM table;

Solution is to change the SQL SELECT:

MapUsers:

Mapping LOAD

  field1,

  u1

  ;

SQL SELECT

field1

(field2 & ' ' & Replace(field3, '_', ' ')) AS u1

FROM table;

4 Replies

Re: Qlik Sense mapping load issue when selecting more than 2 field in sql select

Have you tried giving the second field of the mapping table a name?


Script:

MapUsers:

Mapping LOAD

  field1,

  field2 & ' ' & Replace(field3, '_', ' ') as u1

  ;

SQL SELECT

field1, field2, field3

FROM table;


talk is cheap, supply exceeds demand
Not applicable

Re: Qlik Sense mapping load issue when selecting more than 2 field in sql select

Hi Gysbert,

I tried that also without success. The bug seems to be in loading the SQL SELECT.

Thanks for thinking with me.

Solution is to change the SQL SELECT:

MapUsers:

Mapping LOAD

  field1,

  u1

  ;

SQL SELECT

field1

(field2 & ' ' & Replace(field3, '_', ' ')) AS u1

FROM table;

Not applicable

Re: Qlik Sense mapping load issue when selecting more than 2 field in sql select

I tried with a sample data like below and  it is not created any synthetic key or loop. Please check the below code.

Mappingdata:

Mapping Load

Fruit,

Cost*Quantity as SalesAmount;

LOAD * INLINE [

    Fruit, Cost, Quantity

    banana, 200, 3

    grapes, 100, 4

    apples, 300, 5

    banana, 300, 2

    grapes, 200, 2

    apples, 100, 1

];

//************************

FACT:

Load

CustID,

Address,

ApplyMap('Mappingdata',Fruit,'NA') as Sales;

LOAD * INLINE [

  CustID, Fruit, Address

    1, banana, GA

    2, grapes, CA

    3, apples, MN

    ];

//**************************  

    Fact2:

    Load

SalesManID,

Address as Salesmanaddress,

ApplyMap('Mappingdata',Fruit,'NA') as Sal_Sales;

LOAD * INLINE [

  SalesManID, Fruit, Address

    44, banana, GA

    23, grapes, CA

    34, apples, MN

    ];

Not applicable

Re: Qlik Sense mapping load issue when selecting more than 2 field in sql select

Hi Jhansi,

My problem appears in an SQL select where I want to do a replace (and concat the fields). In a load inline I would never use a replace as I can just type the right values.

Thanks anyway!

Hugo