Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
Appologies if this has been answered elsewhere but I can't find a solution that works for me in the other posts...
My problem is I need to add data and use result in the next row.
Here is what I'm trying to do:
Week no | Opening Balance | Storage | Closing balance |
5 | value coming from a diferent query | opening balance week 5 + pallets in week 5 | Storage week 5 - Pallets out week 5 |
6 | Closing balance week 5 | Opening balance week 6 + pallets in week 6 | Storage week 6 - Pallets out week 6 |
The data I have is Opening balance for the first row as a manual input converted to a simple query
For the pallets in and out I have it coming from another table which looks like the below:
Week no | Pallets in | Pallets out |
5 | 174 | 197 |
6 | 176 | 74 |
There might be a very easy way to this but I can't find the best option
Thanks,
Paul
I'm not sure why you think it would take 4 times... but I don't see your dataset.
Another option is to adjust the week no for closing balance rows to match the other rows, and then do a pivot to get a column for each event type in a week.
Hi Artemus, I must not understand properly your steps then...
Here is the data I'm working with:
A query with my week numbers
A query which has my total pallets in and total pallets out for the different weeks of my data set (some weeks might be 0 and therefore don't show up in that table)
A query which is drilled down to return the opening balance for the first week.
If Week number is n, n+1, etc... I need to create the following table:
Where Opening balance week n is OPWn, Storage is SWn, Closing balance is CBWn
Pallets in week n is PIWn, Pallets out Week n is POWn
Opening balance for the first week is x (manual input)
Week number | Opening balance | Storage | Closing balance |
n | x | SWn= x + PIWn | CBWn = SWn - POWn |
n+1 | OPWn+1 = CBWn | SWn+1 = OPWn+1 +PIWn+1 | CBWn+1 = SWn+1 - POWn+1 |
n+2 | OPWn+2 = CBWn+1 | SWn+2 = OPWn+2 +PIWn+2 | CBWn+2 = SWn+2 - POWn+2 |
I hope that makes more sense and sorry if the solution is the same, I just can't work out what your instructions mean for my data...
Thansk for the quick response artemus.
I had thought of that, but would that mean that I have to do it 4 times? I might take another look in the morning after a while away from the screen 🙂