Exclude the null value

[#1]

Hi everyone ,

I have some issue to load a data table without the null value.

Here is an example of datas :

Ref	Currency rate 1	Currency rate 2
255	-1.36	-1.52529
255	-	-

What I want is to keep every row with currency rate 1 or currency rate 2 different from NULL, so the first row.

Here is what I did in the set analysis :

test:

LOAD [currency rate 1] as currency_rate_1,

     [currency rate 2] as currency_rate_2,

     ref

FROM

(biff, embedded labels, table is Sheet1$);

test2:

NoConcatenate

load *

resident test

where currency_rate_2 > 0;

But when I create a table box with test2 I have the second row too ...

I tested too :

where isnull(currency_rate_2) = 0;

where len(currency_rate_2) > 0;

Can anyone help me to understand why please ?

Kind regards ,

Gwendaline

[sunny_talwar]

I think you need this for testing

test:

LOAD [currency rate 1] as currency_rate_1,

     [currency rate 2] as currency_rate_2,

     ref

FROM

(biff, embedded labels, table is Sheet1$);

test2:

NoConcatenate

LOAD currency_rate_1 as currency_rate_1_TEST,

     currency_rate_2 as currency_rate_2_TEST

Resident test

Where Len(Trim(currency_rate_2)) > 0;

Now check currency_rate_1_TEST and currency_rate_2_TEST

[#3]

Hi Sunny ,

Thanks a lot for your help.

It works with my example but when I use it with other data it doesn't : there is one variable with null value sometimes and it seems that is the source of my issue.

My data :

date	var8	ref	var14	var15	var16	var17	var18	var20	var21	var22	currency rate 1	currency rate 2	var23
11/01/17	SGD	2.553E+09		300	441.44	441.44	3.1E+09	20	2	1	-1.52529		2
11/01/17	SGD	2.553E+09		300	441.44	441.44	3.1E+09	20	2	1		-1.36	2

And the script (I have several variables so I write "..." here but in my script there are all variables) :

test_vrai:

LOAD date,

     ...,

     var23,

     [currency rate 1] as currency_rate_1,

     [currency rate 2] as currency_rate_2

FROM

(ooxml, embedded labels, table is Sheet1);

test_vrai2:

NoConcatenate

load date, ...,

     var23,

     max(currency_rate_1) as max_currency_rate_1,

     max(currency_rate_2) as max_currency_rate_2

resident test_vrai

group by date, ... , var23;

test_vrai3:

NoConcatenate

load date, ... , var23,

     max_currency_rate_1 as max_currency_rate_1_test,

     max_currency_rate_2 as max_currency_rate_2_test

resident test_vrai2

where Len(Trim(max_currency_rate_1)) > 0 ;

My result should be only one row but I have 2 rows : 1 correct with max_currency_rate_1_test different from null and max_currency_rate_2_test different from null and 1 row with null values for these 2 indicators.

I think that now the issue is linked to the group by. What do you think ?

To a better understanding : I use a group by because I have 2 rows : one with the currency rate 1 and another with the currency rate 2 and I want only 1 row. The group by works but the "where" doesn't.

Kind regards ,

Gwendaline

[sunny_talwar]

You might be right.... it might be helpful to look at the output after the group by to see how many rows are available and what are the values.... and proceed from there... if you are able to share a sample qvw to look at... may be we can help better....

[#5]

You will find my excel file and the qvw.

Hope it will help.

Gwendaline

[sunny_talwar · Accepted solution]

I think your script is working just fine... look at the data model view here

You are seeing the null rows because you still have them in the other two tables.... If you add DROP Table test_vrai, test_vrai2; at the end of the script.... you won't see the nulls anymore

[#7]

It works. Thanks a lot Sunny for your help !

Have a nice day,

Gwendaline