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

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

Tue, 18 Aug 2015 12:54:01 GMT

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,

;

SQL SELECT

field1

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

FROM table;

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

Gysbert_Wassenaar — Wed, 19 Aug 2015 12:07:37 GMT

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;

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

Fri, 21 Aug 2015 07:52:56 GMT

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,

;

SQL SELECT

field1

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

FROM table;

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

Fri, 21 Aug 2015 20:43:36 GMT

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

];

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

Mon, 24 Aug 2015 08:49:56 GMT

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