Sample Randomized allocation Logic in Cognos

[kcog123]

Hi All,
If you guys are familier with Randomized allocation in SQL, i need the same logic to be implemented in Cognos.
For Eg:
sample randomized allocation
WHEN (acctnmbr like'11%'
and DPD=0)THEN 1
WHEN (acctnmbr like'11%'
and DPD between 1 and 10)THEN 2
WHEN (acctnmbr like'11%'
and DPD between 10 and 20)THEN 2
WHEN (acctnmbr like'11%'
and DPD between 20 and 30)THEN 2
WHEN (acctnmbr like'10%'
and DPD between 1 and 10)THEN 3
WHEN (acctnmbr like'10%'
and DPD between 10 and 20)THEN 5
WHEN (acctnmbr like'10%'
and DPD between 20 and 30)THEN 5
END
In this case it will give me 1 acct that starts with 11 and has dpd=0,
2 accts that start with 11 and have dpd btwn 1 and 10.....and so on.
I need the same logic in cognos. i did it with unions but it's too many queries.
This is only for one page. i have 12 more pages like this with different allocations.
Please help. Kind of urgent.

Thanks in advance.

 

[blom0344]

I would try to solve this by using a additional table, replacing the case logic by a complex join.

Such a table RANDOM would look like (only first record shown):

ACCTNMBR DPD_LOW DPD_HIGH VALUE

11 1 10 2

It would then be a matter of defining a complex join:

WHERE SUBSTRING(SOMETABLE.ACCTNMBR,1,2) = RANDOM.ACCTNMBR AND
SOMETABLE.DPD BETWEEN RANDOM.DPD_LOW AND RANDOM.DPD_HIGH

This retrieves value 2

So instead of writing a bunch of CASE statements let the join do its work and feed the results to Cognos

Ties Blom

 

[kcog123]

Hi,
Thank you for the response. I think i got what you are saying.
But i cann't create a table or have access to create it and moreover it's too complex because there are 1mn accts.
Can you think of any that can help my situation...with only available data/table?

Thank you so much...

 

[blom0344]

You need to solve this in the backend. Cognos does not - AFAIK - have the functions to generate randomized data.

The fact that you have a million detail accounts does not matter though, since you only scan the first 2 characters in the accountnumber string. This means the supporting table can be very much smaller.

Instead of creating the table you may build an SQL query subject that stores the data virtually..

You can reach me through cognoise.com , that forum supports private messages and attachments. With some sample data I could build you such an query subject definition..

Ties Blom