Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
SuppilerScore:
LOAD
CLIENT,
VENDOR_NAME,
ROLE,
DATE_OF_REVIEW,
QM_SCORE,
CS_SCORE,
GENERAL_SCORE,
PROC_SCORE,
MANU_SCORE,
round(TOTAL_SCORE) as TOTAL_SCORE ,
pick(match(month(DATE_OF_REVIEW),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),
'Q4','Q4','Q4','Q1','Q1','Q1','Q2','Q2','Q2','Q3','Q3','Q3') as QUATERS,
'FY'& Date(AddYears(yearstart (DATE_OF_REVIEW, 1, 4),-1),'YY') &'-'& Date(yearstart (DATE_OF_REVIEW, 1, 4),'YY') as YEAR
FROM
[..\QVDS\SuppilerScore.qvd]
(qvd);
CrossTable(QUALITY,SCORE,8)
LOAD
CLIENT,
VENDOR_NAME as name1,
ROLE,
DATE_OF_REVIEW,
TOTAL_SCORE,
QUATERS,
YEAR,
YEAR&QUATERS AS YEARQUATER ,
QM_SCORE AS [Quality Management],
CS_SCORE as [Customer Service],
GENERAL_SCORE as General,
PROC_SCORE as Procurement ,
MANU_SCORE as Manufacture
Resident SuppilerScore;
DROP Table SuppilerScore;
LEFT join
[Suppiler Rating]:
LOAD TOTAL_SCORE,
SUPCAT
FROM
(ooxml, embedded labels, table is Sheet1);
LEFT join
LOAD CLIENT,
// LIFNR,
VENDOR_NAME as name1,
ROLE,
DATE_OF_REVIEW,
QUES_NO,
ANSWER
FROM
(qvd);
LEFT join
LOAD CLIENT,
QUES_NO,
QUES_DESC
FROM
(qvd);
till this my data flow looks ok
so now i am trying to group particular QUES_NO under each QUALITY VALUE
i mean
suppose for Customer Service(value)
| QUES_NO | QUALITY |
| Q1_CS | Customer Service |
| Q1_DELIV | Customer Service |
| Q1_TECH | Customer Service |
| Q2_CS | Customer Service |
| Q2_DELIV | Customer Service |
| Q2_TECH | Customer Service |
| Q3_CS | Customer Service |
| Q3_DELIV | Customer Service |
| Q3_TECH | Customer Service |
| Q4_DELIV | Customer Service |
i have done all the grouping in excel sheet and loaded in to application
Right JOIN
LOAD QUES_NO,
QUALITY
FROM
(ooxml, embedded labels, table is Sheet1);
after loading I applied RIGHT JOIN b/w this table and rest of the table,then i got wat i am looking
but unfortunately my right side entire data is gone ........
then i applied left join,i got my data back but grouping thing is not working
so anyone suggestion me how to make both the thing to work,i mean data and group
PFA grouping excel sheet
Hi,
as suggested by mindaugasbacius i have used apply map function to group QUES_NO with Quality
map_quality:
mapping LOAD QUES_NO,
QUALITY
FROM
(ooxml, embedded labels, table is Sheet1);
SuppilerScore:
LOAD
CLIENT,
//LIFNR,
VENDOR_NAME,
ROLE,
DATE_OF_REVIEW,
QM_SCORE,
//QM_MAX,
CS_SCORE,
// CS_MAX,
GENERAL_SCORE,
//GENERAL_MAX,
PROC_SCORE,
// PROC_MAX,
MANU_SCORE,
// MANU_MAX,
round(TOTAL_SCORE) as TOTAL_SCORE ,
//MAX_SCORE
//'Q'&Ceil(MONTH(DATE_OF_REVIEW)/3) AS QUATERS,
// year(DATE_OF_REVIEW) as YEAR
pick(match(month(DATE_OF_REVIEW),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),
'Q4','Q4','Q4','Q1','Q1','Q1','Q2','Q2','Q2','Q3','Q3','Q3') as QUATERS,
'FY'& Date(AddYears(yearstart (DATE_OF_REVIEW, 1, 4),-1),'YY') &'-'& Date(yearstart (DATE_OF_REVIEW, 1, 4),'YY') as YEAR
// Year(yearstart (DATE_OF_REVIEW, 1, 4)) as YEAR
FROM
[..\QVDS\SuppilerScore.qvd]
(qvd);
CrossTable(QUALITY,SCORE,8)
LOAD
CLIENT,
VENDOR_NAME as name1,
ROLE,
DATE_OF_REVIEW,
TOTAL_SCORE,
QUATERS,
YEAR,
YEAR&QUATERS AS YEARQUATER ,
QM_SCORE AS [Quality Management],
CS_SCORE as [Customer Service],
GENERAL_SCORE as General,
PROC_SCORE as Procurement ,
MANU_SCORE as Manufacture
Resident SuppilerScore;
DROP Table SuppilerScore;
LEFT join
[Suppiler Rating]:
LOAD TOTAL_SCORE,
SUPCAT
FROM
(ooxml, embedded labels, table is Sheet1);
LEFT join
LOAD CLIENT,
// LIFNR,
VENDOR_NAME as name1,
ROLE,
DATE_OF_REVIEW,
QUES_NO,
ANSWER
FROM
(qvd);
LEFT join
LOAD CLIENT,
QUES_NO,
QUES_DESC,
Applymap('map_quality', QUES_NO, ':') as QUALITY
FROM
(qvd);
After doing that my outcome is this ,which looks ok
now i want to display them in a straight table
Dimeansion:QUES_DESC
Expression:=FirstSortedValue({<ROLE={EVAL}>}ANSWER,-DATE_OF_REVIEW)
by ENABLING suppress null value for QUES_DESC dimenasion
i got this (column with zero value are missing)
so if you think ,the first slide table data flow is a proper flow,then can you plz suggest me wat expression do i need to writing down so that even the column with ZERO answer should also display
