topic Blank data coming from Data source in QlikView

Blank data coming from Data source

arusanah — Thu, 09 Apr 2015 15:45:03 GMT

customer Key
1100/12/90
1320/20/55
//
//
1459/25/10
1500/34/99
//
1100/12/90
1110/22/90

this is the data coming from SAP table .

i tried using following code to remove blank data and it is not working

From <table name>

where (len(trim([Sales Organization_VKORG])) >0 and len(trim(Customer_KUNNR)) > 0 and len(trim([Division extension_DIVEXT]))> 0 );

pls help

Re: Blank data coming from Data source

Karthik3 — Thu, 09 Apr 2015 15:51:59 GMT

// might not be blanks so try Len(TRIM([Sales Org..])) >2 OR WHERE Left(TRIM([Sales Org..]))<>'//'

Re: Blank data coming from Data source

ramoncova06 — Thu, 09 Apr 2015 17:21:39 GMT

you could try with isnull also

(isnull(trim([Sales Organization_VKORG]))=0 and isnull(trim(Customer_KUNNR)) =0 and isnull(trim([Division extension_DIVEXT])) =0 )

though for this to work all three fields must have a value, otherwise use "or" instead of "and"

Re: Blank data coming from Data source

arusanah — Tue, 14 Apr 2015 22:55:42 GMT

still not working

Re: Blank data coming from Data source

ramoncova06 — Wed, 15 Apr 2015 01:58:24 GMT

are the "//" part of the data or is that something you added to denote the null values ?

Re: Blank data coming from Data source

danieloberbilli — Wed, 15 Apr 2015 02:04:20 GMT

are the // characters from the SAP table...so its way of indicating null values?

maybe like

...where trim([customer Key]) <> '//'

Re: Blank data coming from Data source

arusanah — Wed, 15 Apr 2015 14:48:13 GMT

Hi Ramon,

I created a key column were '/' is separator

[sales org]&'/'&[Customer kunnr]&'/'&[Distributed Channel]

Re: Blank data coming from Data source

arusanah — Wed, 15 Apr 2015 14:49:20 GMT

Hi Daniel,

No they were not part of dataset .

I created a key column were '/' is separator

[sales org]&'/'&[Customer kunnr]&'/'&[Distributed Channel]

Re: Blank data coming from Data source

danieloberbilli — Thu, 16 Apr 2015 00:02:18 GMT

There might be more fields to consider in the WHERE ... clause.

Or maybe the where-clause is not working directly when loading from the SAP source directly. Try to load all the data (without 'where') in a table in Qlik and then use a second stage with a noconcatenate load resident where you use use the 'where' clause and drop the previous (full) table

Re: Blank data coming from Data source

arusanah — Thu, 16 Apr 2015 05:06:47 GMT

If key is made up of only three fields from table , what else should we check ?

i'm pulling only 5 field from KNA1 table in SAP in this dimension.

customer Kunnr , division , sales org, distribution channel, division extent

Re: Blank data coming from Data source

ramoncova06 — Thu, 16 Apr 2015 17:42:58 GMT

so just from me to understand better

you extract your data and create this key [sales org]&'/'&[Customer kunnr]&'/'&[Distributed Channel] at what moment is this happening ?

In a table created before the where or during the where ?

if is during the where have you tried

where trim([sales org]&'/'&[Customer kunnr]&'/'&[Distributed Channel]) = '\\'

Re: Blank data coming from Data source

Thu, 16 Apr 2015 18:57:06 GMT

Why not do this,

Load *

Where not isnull(salesorg) and not isnull(Customer kunnr) and not isnull(Distributed Channel);

SQL Select * From KNA1;

Not sure if I am missing something here.

Re: Blank data coming from Data source

arusanah — Wed, 22 Apr 2015 00:28:30 GMT

done that but not working

Re: Blank data coming from Data source

kangaroomac — Wed, 22 Apr 2015 05:28:13 GMT

Hi Arusanah,

For some reason it seems when you load from SAP, it does not recognise the '//' as a field value.

A work-around could be to:

"MyTable":

do the table load..

then do another load

"My New Table":

NoConcatenate Load

....

Where <Field> <> '//';

DROP Table MyTable;

Your root cause though could be "missing" fields (Check Sub Set Ratio's in your data model and use where exists where applicable).