Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
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
];
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