topic Re: Custom Field in QlikView

Custom Field

Anonymous — Tue, 03 Jun 2014 15:31:34 GMT

I have a custom table im trying to bring through from Syspro, but i only want it to show certain lines that have 'LAT001' in them, im presuming i have to use a LOAD * statement before the load but what do i need to state, is it an IF statement? this is what i tried already... what do i need to put in order to "show" 'LAT001' only?

LOAD *,

IF (FieldName = 'LAT001')

//CustomField

LateReason:

LOAD KeyField AS Invoice,

FieldName,

AlphaValue;

SQL SELECT KeyField,

FieldName,

AlphaValue

FROM SysproCompanyA.dbo.AdmFormData;

Re: Custom Field

Tue, 03 Jun 2014 15:38:42 GMT

you wanted to load those load while fieldname field consists LAT001 ???

why dont you write that condition in ur sql

LateReason:

LOAD KeyField AS Invoice,

FieldName,

AlphaValue;

SQL SELECT KeyField,

FieldName,

AlphaValue

FROM SysproCompanyA.dbo.AdmFormData

where FieldName like '%LAT001%'

;

Re: Custom Field

Anonymous — Wed, 04 Jun 2014 10:30:11 GMT

yes that has helped so thank you for that, what I did was design the table in SQL so that it only looks at LAT001 then linked that it to QV, I then renamed the Expr2 as DispatchNote (number) and joined it to mdndetail to link it to salesorder no and dispatch no. worked a treat! The only problem now is that when the reason is blank it shows it on my graph (attached) and can’t think how to get rid… any ideas? It needs to have a formula that says if reason is null don’t show – but how do you write that?

LateReason:

LOAD Expr2 AS DispatchNote,

AlphaValue AS Reason;

SQL SELECT Expr2,

AlphaValue

FROM SysproCompanyA.dbo."JT_Carriage";

LEFT JOIN (LateReason)

LOAD DispatchNote,

SalesOrder;

SQL SELECT DispatchNote,

SalesOrder

FROM SysproCompanyA.dbo.MdnDetail;

Re: Custom Field

MarcoWedel — Wed, 04 Jun 2014 10:44:12 GMT

check the "suppress when value is null" check box in the dimensions tab of your charts properties.

regards

Marco

Re: Custom Field

Wed, 04 Jun 2014 12:18:23 GMT

Use Where condition instead of preceding load

where fieldname='LAT001'

Regards,

Lalit

Re: Custom Field

Wed, 04 Jun 2014 12:26:32 GMT

in your chart expression use set analysis:

for example, count({<Reason -= {'whatever your null values look like here'}>}Reason)

Re: Custom Field

Anonymous — Wed, 04 Jun 2014 13:07:16 GMT

no, tried that first before posting, doesnt change the outcome

Re: Custom Field

Anonymous — Wed, 04 Jun 2014 13:08:26 GMT

just tried that and my null value is blank so entered:

count({<Reason -= {''}>}Reason)

and that didnt work?

Re: Custom Field

Wed, 04 Jun 2014 13:20:19 GMT

You could try count({$<Reason -= "=Null()">}Reason)

Re: Custom Field

Anonymous — Wed, 04 Jun 2014 13:29:18 GMT

nope

Re: Custom Field

Wed, 04 Jun 2014 13:33:49 GMT

Whoops I meant count({$<Reason -= '=Null()'>}Reason) ....I accidentally used double quotes in my post above. If that still doesn't work you could try the expression you mentioned you tried earlier (count({<Reason -= {''}>}Reason)).... but instead count({<Reason -= {' '}>}Reason) just in case there is a blank space in there rather than a null string.

Re: Custom Field

Wed, 04 Jun 2014 14:06:41 GMT

it can be done multiple ways.

try this first:

on your table dimension, use calculated dimension

if(len(trim(Reason))=0,null(),Reason)

then check the 'suppress when value is null.

it that doesn't work ... try below

try to replace blank fields with a text 'NA' in your script.

TempLateReason:

noconcatenate

load DispatchNote, if(len(trim(Reason))=0,null(),Reason) as Reason, SalesOrder resident LateReason;

drop table LateReason;

rename TempLateReason to LateReason;

on your table dimension

then check the 'suppress when value is null.

hope this might work for you.

Re: Custom Field

Anonymous — Wed, 04 Jun 2014 14:13:57 GMT

Nope.

I think what i will do is add another option of "Not Late" in Syspro and add that as default (it will also act as a prompt to the sales guys to change if late. then add the <> dimension in the sql table and then QV will only bring through the others....

thanks anyway

Re: Custom Field

Wed, 04 Jun 2014 14:20:44 GMT

did you try these ?

Re: Custom Field

Anonymous — Wed, 04 Jun 2014 14:39:12 GMT

Tried the first option and it didnt work, the second option kinda fits in with what i may as well do, although im finding that the SQL table will not get rid of my blanks even though it clearly says in the filter "is not null"