Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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
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
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
please provide some sample data and your expected result.
thanks
regards
Marco
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;
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)
HI,
Kindly post the sample application.
Regards,
Kaushik Solanki
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
Thank u Marco its working