Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All
I got struck with an requirement which i am able to achieve partially .the actual requirement data seem to be like this
table1:
location score quater date(mm/dd/yyyy)
mumbai 100 q1 03/12/2015
mumbai 101 q2 06/20/2015
mumbai 103 q3 09/05/2015
pune 200 q1 03/12/2015
pune 100 q2 06/20/2015
the requirement was to achieve current score ,for this i wrote the expression by using firstsortvalue which look like this
straight table:
dimension :location
expression : FirstSortedValue(score,-date) ,FirstSortedValue(quater,-date)by this i can achieve current date max score which i got perfect, but now they have extended the requirement a level up
Result:
location score quater
mumbai 103 q3
pune 100 q2
i achieved this task by writing the above expressions.
Now the requirement is like this
table2:(A SAMPLE DATA NOT ACCURATE)
DATE Quality Governance Sampling Lab Analysis Failure Management Supplier Management RM Specifications score
03/12/2015 20 70 100 150 10 20 100
06/20/2015 10 10 10 20 10 10 101
09/05/2015 30 20 20 30 10 10 103
03/12/2015 10 20 50 10 20 10 200
06/20/2015 10 40 20 50 40 10 100
So now the requirement is when i press 103(score field) From the result table it should show be like this
FINAL TABLE
location Quality Governance Sampling Lab Analysis Failure Management Supplier Management RM Specifications score
mumbai 30 20 20 30 10 10 103
Pune 10 40 20 50 40 10 100
Everything is predefine only my concern is how to dispaly them for my client ,i mean shall I display both the tables in same sheet or shall i wirte trigger so that after pressing 103 (SCORE FIELD) Result table it will bring me to Final table which is in sheet 02 ...
is there any chance to group all these fields (Quality Governance Sampling Lab Analysis Failure Management Supplier Management RM Specifications) in a single group and display everything in one table instead of two different tables in pivot
like this
final table:(pivot table)
location group score quater
mumbai + 103 q3
pune + 100 q2
thanks
naveen
Hi, naveen kumar.
If you create a composite key in your data model would no solve your problem? Ex.:
Table1:
Load
autonumber(score& '_' & Date(date)) as [%Composite Key]
location,
score,
quater
from Table1;
Table2:
autonumber(score& '_' & Date(DATE)) as [%Composite Key]
DATE,
Quality,
Governance,
Sampling,
[Lab Analysis],
[Failure Management],
[Supplier Management],
[RM Specifications],
score
from Table2;
Regards,
Jonas Melo.