topic Re: Loading issue - involving IF statement in QlikView

Loading issue - involving IF statement

Mon, 16 Dec 2013 11:51:12 GMT

Hey guys,

I'm trying to load the following script into Qlikview:

Load

[Client Name] As Client_Name,

[Account Number],
[Portfolio Name],
if(len(Client_Name) = 0, 'Fail','Pass') as Client_Name_No_Data
// if([Client Name] = "0", 'Fail','Pass') as Client_Name_Wrong_Data,
// if(sum([Client Name]) > 0, 'Numerical','Alphabetical') as [Alphabetical/Numerical],

From

Having issues as it wont recognise the 'Client Name'. I've tried it without renaming [Client Name] As Client_Name as well. When loading with out the If Statement - it works fine.

Can anybody assist please?

Kind Regards

Mav

Re: Loading issue - involving IF statement

tresB — Mon, 16 Dec 2013 11:59:16 GMT

Try:

if(len([Client Name] ) = 0, 'Fail','Pass') as Client_Name_No_Data

Re: Loading issue - involving IF statement

Mon, 16 Dec 2013 13:11:12 GMT

Hey guys,

That seemed to have worked, but I encountered another issue. The below statement returns an error in the statement which I cant seem to locate.

if(sum([Client Name]) => 0, 'Numerical','Alphabetical') as [Alphabetical/Numerical]

Re: Loading issue - involving IF statement

tresB — Mon, 16 Dec 2013 13:16:59 GMT

Sum is an aggregation function. If you use one, there has to be a 'Group By' clause. Are you using that? What is the error message you are getting?

Re: Loading issue - involving IF statement

Mon, 16 Dec 2013 13:20:22 GMT

Error in expression ')' expected. No I haven't used group by? I'm fairly new to scripting in QV altogether.

Re: Loading issue - involving IF statement

tresB — Mon, 16 Dec 2013 13:32:33 GMT

Please post your sample app or atleast the script section

Re: Loading issue - involving IF statement

Mon, 16 Dec 2013 13:34:38 GMT

LOAD [Client Name],
    [Account Number],
    [Portfolio Name],
    [Transaction Identifier],
    [Firm’s Internal Transaction Reference],
    [Transaction Date],
    [PO Instrument Name],
    [FCA Qualified Transaction],
    [FCA Non Qualified Transaction Reason],
    [FCA Sent Transaction],
    [FCA Rejected Transaction],
    [Transaction Type],
    [Instrument Type],
    IPO,
    [Trading Capacity],
    [Trading Exchange],
    [Account Type],
    if(len([Client Name]) = 0, 'Fail','Pass') as Client_Name_No_Data,
    if([Client Name] = 0, 'Fail','Pass') as Client_Name_Wrong_Data,
    if(sum([Client Name]) => 0, 'Numerical','Alphabetical') as [Alphabetical/Numerical]
FROM

Re: Loading issue - involving IF statement

tresB — Mon, 16 Dec 2013 13:44:50 GMT

If you are only checking if the client name is a text or number then don't use sum() function. Try like:

if( IsNum([Client Name]), 'Numerical','Alphabetical') as [Alphabetical_Numerical]

Re: Loading issue - involving IF statement

Mon, 16 Dec 2013 14:04:11 GMT

It worked!

Fantastic!

Much appreciated!