Hi LiRon
thank for your answer and sorry for reply .
i have attached tha data sample in that if cutomer name is repating i want only that data whose customercode value is greater
with and my report format shown in bleow
| VIN NO | Customer Name | Dealer Name | Dealer Code | Region | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 | Q8 |
|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
here is my script
Question_Master:
LOAD FileBaseName() as x,
No. as QuestionNo,
Question as Question_Dec
FROM
(biff, embedded labels, header is 1 lines, table is [Reporting 1 - Dealer (Month)$], filters(
Remove(Row, RowCnd(Interval, Pos(Top, 2), Pos(Top, 9), Select(1, 0))),
Remove(Row, RowCnd(Interval, Pos(Top, 10), Pos(Top, 36), Select(1, 0))),
Remove(Col, Pos(Top, 52)),
Remove(Col, Pos(Top, 51)),
Remove(Col, Pos(Top, 50)),
Remove(Col, Pos(Top, 49)),
Remove(Col, Pos(Top, 48)),
Remove(Col, Pos(Top, 47)),
Remove(Col, Pos(Top, 46)),
Remove(Col, Pos(Top, 45)),
Remove(Col, Pos(Top, 44)),
Remove(Col, Pos(Top, 43)),
Remove(Col, Pos(Top, 42)),
Remove(Col, Pos(Top, 41)),
Remove(Col, Pos(Top, 40)),
Remove(Col, Pos(Top, 39)),
Remove(Col, Pos(Top, 38)),
Remove(Col, Pos(Top, 37)),
Remove(Col, Pos(Top, 36)),
Remove(Col, Pos(Top, 35)),
Remove(Col, Pos(Top, 34)),
Remove(Col, Pos(Top, 33)),
Remove(Col, Pos(Top, 32)),
Remove(Col, Pos(Top, 31)),
Remove(Col, Pos(Top, 30)),
Remove(Col, Pos(Top, 29)),
Remove(Col, Pos(Top, 28)),
Remove(Col, Pos(Top, 27)),
Remove(Col, Pos(Top, 26)),
Remove(Col, Pos(Top, 25)),
Remove(Col, Pos(Top, 24)),
Remove(Col, Pos(Top, 23)),
Remove(Col, Pos(Top, 22)),
Remove(Col, Pos(Top, 21)),
Remove(Col, Pos(Top, 20)),
Remove(Col, Pos(Top, 19)),
Remove(Col, Pos(Top, 18)),
Remove(Col, Pos(Top, 17)),
Remove(Col, Pos(Top, 16)),
Remove(Col, Pos(Top, 15)),
Remove(Col, Pos(Top, 14)),
Remove(Col, Pos(Top, 13)),
Remove(Col, Pos(Top, 12)),
Remove(Col, Pos(Top, 11)),
Remove(Col, Pos(Top, 10)),
Remove(Col, Pos(Top, 9)),
Remove(Col, Pos(Top, 8)),
Remove(Col, Pos(Top, 7)),
Remove(Col, Pos(Top, 6)),
Remove(Col, Pos(Top, 5)),
Remove(Col, Pos(Top, 4)),
Remove(Col, Pos(Top, 3))
));
Customer_Master:
CrossTable(Question_Dec,Rating,9)
LOAD Question as CustomerCode,
[Customer Name],
[Vehicle Registration No.],
[Dealer Name],
[Dealer Code],
Region,
[VIN No.],
[Engine No.],
[Service Advisor],
"Ease of arranging service visit & Flexibility to accommodate your schedule?[Appointment as per your desired date and time]",
[Service Advisor overall Behavior: Greetings, Helpfulness at Receiving time & Delivery time,Courtesy,friendliness,Responsiveness & Thoroughness of explanation of Service Advisor],
[Dealership Facility Feedback: Ease of driving in & out of facility,Convenience of Dealership location,Cleanliness of dealership & Comfort of waiting area],
"Fairness of the charges [Was the Final amount charged at the time of delivery close to the initial estimate at the time of reception?]",
"Total time required to service your vehicle [Dealer took Appropriate Time]",
"Thoroughness of maintenance/repair work performed [Was the Maintenanc/Repair done as per your request?]",
"Condition/ cleanliness of vehicle on return [Was the vehicle cleaning done as per your request?]",
[Please rate your overall service experience]
FROM
(biff, embedded labels, header is 1 lines, table is [Reporting 1 - Dealer (Month)$], filters(
Remove(Col, Pos(Top, 1)),
Remove(Row, RowCnd(Interval, Pos(Top, 18), Pos(Top, 44), Select(1, 0))),
Transpose()
));
CSI_Weighted:
LOAD [VIN No.],
[Service Advisor],
"CSI Score [Weighted Average]" as CSI_Score
FROM
(biff, embedded labels, header is 1 lines, table is [Reporting 1 - Dealer (Month)$], filters(
Remove(Row, Pos(Top, 8)),
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1)),
Remove(Row, Pos(Top, 16)),
Remove(Row, Pos(Top, 15)),
Remove(Row, Pos(Top, 14)),
Remove(Row, Pos(Top, 13)),
Remove(Row, Pos(Top, 12)),
Remove(Row, Pos(Top, 11)),
Remove(Row, Pos(Top, 10)),
Remove(Row, Pos(Top, 9)),
Remove(Row, Pos(Top, 8)),
Remove(Row, Pos(Top, 7)),
Remove(Row, Pos(Top, 6)),
Remove(Row, Pos(Top, 5)),
Remove(Row, Pos(Top, 4)),
Remove(Row, Pos(Top, 3)),
Remove(Row, RowCnd(Interval, Pos(Top, 4), Pos(Top, 23), Select(1, 0))),
Remove(Col, Pos(Top, 1)),
Transpose()
));
