Asking an OBIEE analysis question.
We have a serialized inventory SA and we have an analysis that shows our entire stock with several columns of information. However, some items have been added to our inventory that are not serialized so the column for serial numbers is blank. No problem, I just added a formula to the serial column so if the serial is null, just replace it with a word or number (right now I have it set to 0) but another issue rose up. You see, since our inventory was previously serialized-only items, we had a quantity column that reported 1 for each of these unique items. Now with non-serialized items in our inventory, the quantity might be something other than 1. This creates an issue since we usually pull this analysis into a CSV and import to an arcane system that reads each line as one single unique item.
Is there any formula I can use in the serial number column that can perhaps see that the quantity is 4 and just create 4 lines in the analysis for this non-serialized item with pseudo serial numbers such as noserial 0, noserial1, noserial2, and noserial3? This way, each line is still a unique serial number for each physical item we have in our inventory.
| shelf | item | serial | quantity |
|---|---|---|---|
| Shelf12 | ARMCPU | ARM3020 | 1 |
| Shelf12 | ARMCPU | ARM1231 | 1 |
| Shelf12 | ARMCPU | ARM1299 | 1 |
| Shelf15 | INTELCPU | INT1221 | 1 |
| Shelf17 | AMDCPU | AMD9282 | 1 |
| Shelf17 | AMDCPU | AMD8742 | 1 |
| Shelf19 | 100CAP | 0 | 2 |
| Shelf20 | 080CAP | 0 | 8 |
The above is how it looks like now. How I would ideally like it be is:
| shelf | item | serial | quantity |
|---|---|---|---|
| Shelf17 | AMDCPU | AMD8742 | 1 |
| Shelf19 | 100CAP | NOSER00 | 1 |
| Shelf19 | 100CAP | NOSER01 | 1 |
| Shelf20 | 080CAP | NOSER02 | 1 |
| Shelf20 | 080CAP | NOSER03 | 1 |
| Shelf20 | 080CAP | NOSER04 | 1 |
| Shelf20 | 080CAP | NOSER05 | 1 |
| Shelf20 | 080CAP | NOSER06 | 1 |
| Shelf20 | 080CAP | NOSER07 | 1 |
| Shelf20 | 080CAP | NOSER08 | 1 |
| Shelf20 | 080CAP | NOSER09 | 1 |
Thank you.
Not sure how is the underlying aggregation in the analysis.
This should create a different serial for each NULL entry.
Use RSUM(1) in the calculated column.
Ex.: IFNULL("SERIAL",'NOSERIAL'||CAST(RSUM(1) as CHAR(10))