Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a transaction table, using 2 different dimensions say Region_a, Region_b of that transaction I have created a new dimension say Region.
This new Region column is loaded as Inline(there could be a better way) script as follows:
Load Region_a
Resident Transaction;
Concatenate
Load Region_b
Resident Transaction;
The reason for doing this is that I need to create a Pivot Table which needs to have all the Regions and the Pivot table needs to be like below:
Region | Region_a Sales | Region_b Sales | Difference |
A | 20 | 30 | |
B | 40 | 10 | |
C | 60 | 30 | |
D | 70 | 70 | |
E | 65 | 60 | |
F | 55 | - |
Difference can be calculated once am able to calculate Sales. Is there anyway using Set Analysis I can write that if Region matched Region_a then give me sales or any other better method. I have tried Sum (if), Set analysis stating Region=Region_a but none of them are working. Strange thing sum(if) is not giving me correct data for all the regions. Any help would be great!
Thanks in advance!
@swuehl , @sunny_talwar Any suggestions? I have found a fix by making Region columns as per the specific concatenated tables in the transaction but am not that happy with this fix, looking for a better solution. Please help
Hi, I am not entirely sure I understand what you are trying to do. Would you be able to share the solution that you implemented which might give idea as to what you are trying to do.
@sunny_talwar Apologies for the late reply! Even though all the transaction tables are concatenated, there are some tables having salesamount for Region_a mapping and others having sales of region_b, so I created a common field region as below:
Transaction:
Load*,Region_a as region
Resident xyz.qvd;
concatenate
Load *,Region_b as region
resident gef.qvd;
I did this for all the tables concatenated and then I used the sum as required.
Earlier, I tried creating an island table which concatenated all the regions. After that I was trying to use sum(if) but that didn't work. Luckily the tables were little different and this method worked but, there would definitely be a better way.
I also tried linking that data island but since some tables had Region_a and other had Region_b, it didn't work.
Hope this explains!