topic Replacing the existing data in QlikView

Replacing the existing data

Wed, 06 Oct 2010 07:45:15 GMT

Hi,

I have a field (Year / Quarter) with the following data

Year / Quarter
2008/01
2008/02
2008/03
2008/04
2009/01
2009/02
2009/03
2009/04

Now I want to change the data by using if condition like

=If(Year / Quarter='2008/01',200801,0)

the results are
Year / Quarter
200801
0
0
0
0
0
0
0

But I want the data like this way
Year / Quarter
200801
200802
200803
200804
200901
200902
200903
200904

Can Any one guide me what is the formula?

Thanks,

Replacing the existing data

renjithpl — Wed, 06 Oct 2010 07:52:48 GMT

Hi,

Try, =Text(Year)&Text(Quarter)

Replacing the existing data

Wed, 06 Oct 2010 07:58:50 GMT

No Ranjit,

its a single field and I want that field should be numeric because I have to use for comparision purpose.

Thanks,

Replacing the existing data

renjithpl — Wed, 06 Oct 2010 08:13:26 GMT

Hi tims,

i think replace function will work,

Try this, in your load

Yearquarter:

LOAD Year,

Quarter,

[Year/Quarter],

Replace([Year/Quarter],'/', '') as [Year/Quarter1]

FROM

YearQuarter.xlsx

(ooxml, embedded labels, table is Sheet1);

pleae let me know , if you have any other issues.

regards

REn

Replacing the existing data

Wed, 06 Oct 2010 08:57:21 GMT

Fine Ranjit,

assume that I have data like this

Year / Quarter
200801
200802
200803
200804
200901
200902
200903
200904

I created one formula

=If([Year / Qtr]=[Year / Qtr],'qmem://<bundled>/BuiltIn/arrow_n_g.png','qmem://<bundled>/BuiltIn/arrow_s_r.png')

Its working fine only equal operator if I use less than (<) or greaterthan(>) then its taking else condition.

why?

Thanks,

Replacing the existing data

Wed, 06 Oct 2010 09:09:56 GMT

I think your [Year/Qtr] is actually a string as QlikView sees it:

Try casting it to numeric:

num#(Replace([Year/Quarter],'/', '')) as [Year/Quarter1]

Regards,

Miha

Replacing the existing data

Wed, 06 Oct 2010 09:27:57 GMT

Still same issue, its working only equal operator not for less than or greater than operators.

Thanks,

Replacing the existing data

Wed, 06 Oct 2010 09:29:15 GMT

Can you post, maybe, your application?

Replacing the existing data

renjithpl — Wed, 06 Oct 2010 10:09:50 GMT

Hi tims,

Explain your condition you want to perform in your application.

I dont think that condition will work. For eg.( 200801 = 2000801) then first condition will satisfy. and If you want greater than condition. For eg (200801 >2000801), its false, because 200801 is not greater than 200801. it will be always equal.

You can explain the condition you want to do, then we can try.

Regards

Ren.