Custom Field

[Anonymous]

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;

[#2]

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%'

;

[Anonymous]

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;

[MarcoWedel]

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

regards

Marco

[#5]

Use Where condition instead of preceding load

where fieldname='LAT001'

Regards,

Lalit

[#6]

in your chart expression use set analysis:

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

[Anonymous]

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

[Anonymous]

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

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

and that didnt work?

[#9]

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

[Anonymous]

nope