Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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
Author

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
Author

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
Author

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