Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tgolding
New Contributor

Include only blank or or <null> values on load

Sorrry, newbie and self taught with Qlick Sense.  Hopefully will outline correctly what is needed.

I know there have been several discussion around this and I have tried various suggested solutions but to no avail.  I am accessing data through sql file, there is one field labeled "ReversalIndicator" that includes the following values; '1', '2', and a blank or null.  I would like to only include the blank or null fields on the load script but am unable to figure how to do so.  I understand that Qlik doesn't recognize blank or null fields unless you tell it to so I have done the following;

added to set analysis

SET NullValue=',Unkown'

Added at the top of the load statement

NullAsValue ReversalIndicator;

I currently have a Where statement already included;

PaidHistory where Filldate>='date' and ReversalIndicator='<Unknown>'

Thanks for any help or guidance.

11 Replies
vvvvvvizard
Contributor III

Re: Include only blank or or <null> values on load

Where len(trim(reversalindicator ))=0;

Luminary
Luminary

Re: Include only blank or or <null> values on load

Exactly as Yusuuf suggests, plus you can get rid of NullValue and NullAsValue variables.

tgolding
New Contributor

Re: Include only blank or or <null> values on load

Thanks for the quick replay however on load data it gives me an "error occurred" message when trying to load the script.

The following error occurred:

Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'trim' is not a recognized built-in function name.

FROM data.dbo.PaidHistory where Filldate>='01/01/2017' and len(trim(ReversalIndicator))='0'

also tried just

FROM data.dbo.PaidHistory where len(trim(ReversalIndicator))='0'

Luminary
Luminary

Re: Include only blank or or <null> values on load

Ah, if you want to use this in the SQL part of the script, ten you need to use SQL equivalents of those functions. Alternatively, you can use this exact condition in a preceeding load statement.

chris3669
New Contributor III

Re: Include only blank or or <null> values on load

With connections, you need to use the source functions, so it is SQL Server (from what the error tells) "is not null" should work.

   FROM data.dbo.PaidHistory where ReversalIndicator is not null

devarasu07
Honored Contributor II

Re: Include only blank or or <null> values on load

Hi,

load * FROM data.dbo.PaidHistory where Filldate>='01/01/2017' and

( len(ReversalIndicator)=0 or  ReversalIndicator is null )

or

load * FROM data.dbo.PaidHistory where Filldate>='01/01/2017' and  ReversalIndicator is null

Note: also check your database date format, if it's different then u have to update it

Thanks,Deva

tgolding
New Contributor

Re: Include only blank or or <null> values on load

Deva, thanks for the suggestion, but this did not work either.  I don't think it is recognizing the blank field as any value.  Again the reversalindicator field has a value of 1, 2, or is blank.

devarasu07
Honored Contributor II

Re: Include only blank or or <null> values on load

Hi,

Issue looks strange. Can you share the mock data and expected output in excel format. that would be easy for us to check and help you

tgolding
New Contributor

Re: Include only blank or or <null> values on load

Sorry for the delayed reply, still working on the issue.  Below is an example, fairly straight forward (I hope).  In the data base I pull from for this data it includes a field call "reversalIndicator".  The data fields where their is a '1' or '2' loaded is not relevant, they are duplicates in a sense based on us reprocessing an invoice at a later time.  What is only needed is the rows where the "reversalindicator" is blank.  The columns with '1' and '2' is essentially old data on the same groupID or invoice and doesn't the final invoice which is associated with the reversalindicator field that is blank.

  This is what is being loaded      

NameGroupIdGroupNameReversalIndicatorvalue
AMBER123Green5
AMBER123Green20
AMBER123Green10

This is what I want to be loaded.

    

NameGroupIdGroupNameReversalIndicatorvalue
AMBER123Green-5

My understanding is that Qlik doesn't immediately recognize blank fields at all(?).

On that particular load statement I have a where statement that limits the date range on the data that is loaded.  It looks like this;:

From Data.dbc.history where Filldate>='01/01/2016'

Community Browser