topic Re: Explain this Urgent in QlikView

Need Urgent for this

Thu, 16 Jan 2014 10:25:03 GMT

, if(isNULL([Sales Territory ID & Desc]),

if(right(SLS_ORG_KEY,3)='LOG','Company:' & left(SLS_ORG_KEY,2) & '; Department:' & trim(purgechar(mid(SLS_ORG_KEY,3),'LOG')) & '; Finance and Complaint Data',SLS_ORG_KEY),

[Sales Territory ID & Desc]) AS 'Sales Territory ID & Desc'

RESIDENT Sales_Org_Tab;

in this one I want 079 but am getting 79 only ,other should be coming properly ,this 79 only not coming properly

Ex :

sales territory id & desc = 009 SEMPRINI ROBERTO

sales org_key = N1067009

---------------------

in this table

SLS_ORG_KEY = N104s079

Sales Territory ID & Desc = 79 - wrong result

Sales Territory ID & Desc = 079 - correct result , remaining all are getting ,but this only getting error

Re: Explain this Urgent

ramkrishna86 — Thu, 16 Jan 2014 10:32:43 GMT

What do u want to understand?

Re: Explain this Urgent

sudeepkm — Thu, 16 Jan 2014 10:52:01 GMT

The expression you put has bunch of string functions to identify certain pattern and based on which a new field is created. The original table where you have the source field ("SLS_ORG_KEY") is in Sales_Org_Tab table.

I hope you are already familiar with Right, Left and mid functions. Please let us know what exact info you would like to gather from the expression.

Re: Explain this Urgent

Peter_Cammaert — Thu, 16 Jan 2014 10:53:04 GMT

This is what it says in pseudo code:

IF [Sales Territory ID & Desc] = NULL THEN

IF SLS_ORG_KEY ends in 'LOG' THEN

LET [Sales Territory ID & Desc] = concatenation of various texts and values

ELSE

LET [Sales Territory ID & Desc] = SLS_ORG_KEY

END IF

Apparently, your example already had a value in [Sales Territory ID & Desc] because its value is neither the concatenation of various texts and values nor the value of SLS_ORG_KEY.

Best,

Peter

Re: Explain this Urgent

Thu, 16 Jan 2014 11:30:51 GMT

Thanks for reply but my requirment is this

in this table

SLS_ORG_KEY = N104s079

Sales Territory ID & Desc = 79 - wrong result

Sales Territory ID & Desc = 079 - correct result , remaining all are getting ,but this only getting error

Re: Need Urgent for this

Peter_Cammaert — Tue, 21 Jan 2014 08:36:54 GMT

Are you sure Sales Territories 079 and 430 do have a description?

Because to me it seems like their description is blank/NULL, and in your objects QlikView decides that 079 looks exactly like a number, omits the leading zero and right-aligns the result. It isn't wrong, it's just not treated as TEXT.

In your LOAD script, you could use the TEXT() function to force QlikView to always treat your [Sales Territory ID & Desc] field as text. That may fix the alignment and disappearing leading zero.

Good luck,

Peter