Include only blank or or <null> values on load

[tgolding]

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.

[vvvvvvizard]

Where len(trim(reversalindicator ))=0;

[juraj_misina]

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

[tgolding]

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'

[juraj_misina]

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]

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]

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]

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]

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]

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      

Name	GroupId	GroupName	ReversalIndicator	value
AMBER	123	Green		5
AMBER	123	Green	2	0
AMBER	123	Green	1	0

This is what I want to be loaded.

Name	GroupId	GroupName	ReversalIndicator	value
AMBER	123	Green	-	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'