topic Re: Help in Code in QlikView

Help in Code

Sun, 07 Jun 2015 07:17:57 GMT

Hi All

i need help please.

in pivote table there is one column share values it contain values 0-3000 values.

i need to divide this values into three category as silver,gold,platinum

this are shown in list box ,when i click on silver i should show only values related to

it like 0-1000.

this is My code is not working

Share Group:

LOAD

Key1,

if(count(ShareNUMBER)>2000,'platinum',

if(count(ShareNUMBER)<1000 and count(ShareNUMBER)>=2000 ,'gold',

if(count(ShareNUMBER)<1000 ,'Silver'))) as Parameternew

Resident Share value Group by Key1;

Re: Help in Code

kaushiknsolanki — Sun, 07 Jun 2015 07:20:59 GMT

Try this,

Temp:

Load Key1,

if(Count>2000,'platinum',

if(Count<1000 and Count>=2000 ,'gold',

if(Count<1000 ,'Silver'))) as Parameternew;

Load Key1,count(ShareNUMBER) as Count

Resident Share value Group by Key1;

Regards,

Kaushik Solanki

Re: Help in Code

patroser — Sun, 07 Jun 2015 07:30:41 GMT

As I understand it, there are already values of 0-3000 in the field ShareNUMBER, correct?

Therefore this code should work:

Share Group:

LOAD

Key1,

if(ShareNUMBER >= 2000, 'platinum',

if(ShareNUMBER < 2000 and ShareNUMBER >= 1000 ,'gold',

if(ShareNUMBER < 1000 ,'Silver'))) as Parameternew

Resident [Share value];

I deleted the Group By, because it didn't make any sense for me.

I also corrected the if statement, now it's platinum (2000-3000), gold (1000-2000) and silver (less than 1000)

You also need to put the table names separated by blanks into []

Patrick

Re: Help in Code

MarcoWedel — Sun, 07 Jun 2015 08:15:30 GMT

please provide some sample data and your expected result.

thanks

regards

Marco

Re: Help in Code

jonathandienst — Sun, 07 Jun 2015 09:16:52 GMT

Your second condition is the wrong way round and will never be true. It should be like this:

if(Count>2000,'platinum',

if(Count>1000 and Count<=2000 ,'gold',

if(Count<=1000 ,'Silver'))) as Parameternew;

although it could be slightly simplified

if(Count>2000, 'platinum',

if(Count>1000, 'gold', 'silver')) as Parameternew;

Re: Help in Code

Sun, 07 Jun 2015 09:51:12 GMT

Re: Help in Code

Sun, 07 Jun 2015 10:13:16 GMT

it is working but iam getting all vales like

if(Count>=2000,'platinum', ( result is 0-3000 values)

if(Count<1000 and Count>=2000 ,'gold', ( result is 0-2000)

if(Count<1000 ,'Silver'))) as Parameternew; (result 0-1000)

Re: Help in Code

kaushiknsolanki — Sun, 07 Jun 2015 10:16:17 GMT

HI,

Kindly post the sample application.

Regards,

Kaushik Solanki

Re: Help in Code

MarcoWedel — Sun, 07 Jun 2015 22:14:20 GMT

Hi,

another solution could be Intervalmatch():

tabShareLevel:
LOAD AutoNumberHash128(MinShare, MaxShare) as %ShareLevelID,
    MinShare,
    MaxShare,
    Dual(ShareLevel,RecNo()) as ShareLevel
INLINE [
    MinShare, MaxShare, ShareLevel
    0, 999, Silver
    1000, 1999, Gold
    2000, 3000, Platinum
];

tabShareGroup:
LOAD RecNo() as Key1,
    Ceil(Rand()*3000) as ShareNUMBER
AutoGenerate 30;

Left Join (tabShareGroup)
IntervalMatch (ShareNUMBER)
LOAD MinShare, MaxShare
Resident tabShareLevel;

Left Join (tabShareGroup)
LOAD Distinct
    MinShare,
    MaxShare,
    AutoNumberHash128(MinShare, MaxShare) as %ShareLevelID
Resident tabShareGroup;

DROP Fields MinShare, MaxShare From tabShareGroup;

hope this helps

regards

Marco

Re: Help in Code

Mon, 08 Jun 2015 05:27:43 GMT

Thank u Marco its working