topic Keeping the dot.... in QlikView

Keeping the dot....

pkelly — Wed, 10 Nov 2010 19:44:15 GMT

I have an extract from our sales invoice warehouse which I am trying to link to our customer "Ship To" file to get up to date values such as the current rep for the delivery address.

The issue I have is that, in their wisdom, one of our users has created ship to codes with a "." at the end...

So for customer "ABC" I have ship to codes "1234" and "1234."

As a key for the link between the two tables I have concatenated customer with ship to.

In my sales invoice warehouse extract I have...

ABC-1234

ABC-1234.

When I build my extract from the customer ship to table, I only get "ABC-1234"

There are two records but the extract is dropping the "." from the end of the codes which is breaking my link between the two tables.

I have tried everything that I can think of to keep this from using cast as char in the sql to text() in the load with no success.

Any suggestions on how to get round this greatly appreciated.

Thanks

Paul

Keeping the dot....

Wed, 10 Nov 2010 19:58:57 GMT

Did you try text() in your field on load instead of on SQL?

Maybe it works!!!!

Keeping the dot....

pkelly — Wed, 10 Nov 2010 22:29:26 GMT

Hi Erico..

I have used the TEXT() command in my load - no success...

Regards

Paul

Keeping the dot....

Wed, 10 Nov 2010 23:19:13 GMT

Hi Paul,

I rebuild your example with my local oracle database and loaded some data via ODBC into a simple QV. I had no success as the data was as I expected it: all rows were correctly loaded (ie I have one "1234" and one "1234." in my source table and in my qv-table)

There are some questions to fetch the whole picture: what database you are using, which type of connection, of what type is your db-field. And which part of the whole process "forgets" your dot ? Is it i.e. the ODBC-driver which interprets the dot as a thousand-seperator? How is the result using a flat file as stage? Did you try something like to concat your ship to code with an single char so your resulting SQL-column is at any case an char-field?

Regards, Roland

Keeping the dot....

danielrozental — Thu, 11 Nov 2010 01:50:49 GMT

Paul, I agree with Roland that Text(Field) should be good enough, can you try posting the sql load sentence so we can check it?

Keeping the dot....

deepakk — Thu, 11 Nov 2010 05:32:30 GMT

Other way around

Is it possible to reverse the key in both the tables like '123.-ABC' and then link the two tables.

Or remove the ". " from the data so it will become easy for you to link it.

Keeping the dot....

Thu, 11 Nov 2010 08:58:13 GMT

Hi,

probably you already tried this, but did you try the text command in the QlikView part of the script or in the SQL part?
I noticed before that leading zeros have to be handled in the SQL part and not in the QlikView script, this might be the same issue.
Best of luck.

Rgds,
Jonas

QlikView Consultant at Optivasys | http://www.optivasys.com

Keeping the dot....

Thu, 11 Nov 2010 09:14:40 GMT

Hi,

Try to concatenate 'x' before and after in the SQL SELECT part, and drop those in the LOAD .


LOAD mid(Customer, 2, len(Customer) - 2) ;
SQL SELECT 'x' || Customer || 'x' ...

-Alex

Keeping the dot....

pkelly — Thu, 11 Nov 2010 10:36:00 GMT

Thanks to evryone for their suggestions...

What I ended up with (and it appears to work) is...

TempCustomerShipTo:
LOAD
UPPER(cust) AS %CustKey,
IF("active" = 0, 'No', 'Yes') As cst_Active,
UPPER(cust) AS cst_CustomerCode,
name AS cst_CustomerName,

TEXT(ship) AS tmpShip,

add1 AS cst_Add1,
add2 AS cst_Add2,
add3 AS cst_Add3,
city AS cst_City,
pc AS cst_PostCode,
UPPER("geo-area") AS cst_TransportArea,
UPPER(Prov) AS cst_Prov,
UPPER("external-rep") AS cst_ExternalRep,
UPPER("silverman-rep") AS cst_AccountManager,
UPPER(shipterms) AS cst_ShipTerms,
ctry AS cst_Country;
SQL SELECT
active,
add1,
add2,
add3,
city,
ctry,
cust,
"external-rep",
"geo-area",
name,
pc,
Prov,

ship,

shipterms,
"silverman-rep"
FROM PUB."cus_ship";

CustomerShipTo:
LOAD *,
UPPER(cst_CustomerCode) & '-' & UPPER("cst_ShipToCode") AS %CustShipToKey;
LOAD * ,
REPLACE(LTRIM(REPLACE(tmpShip, '0', ' ')), ' ', 0) AS cst_ShipToCode
RESIDENT TempCustomerShipTo;

DROP TABLE TempCustomerShipTo;
DROP FIELD tmpShip;

This removes all the leading zeros and keeps the full stop in the key and allows me to link to my sales detail.

Will do more testing but initial test look good...

Regards

Paul