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
Added at the top of the load statement
I currently have a Where statement already included;
PaidHistory where Filldate>='date' and ReversalIndicator='<Unknown>'
Thanks for any help or guidance.
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'
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.
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
load * FROM data.dbo.PaidHistory where Filldate>='01/01/2017' and
( len(ReversalIndicator)=0 or ReversalIndicator is null )
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
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.
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
This is what I want to be loaded.
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'