25 Replies Latest reply: Jul 25, 2011 11:37 PM by BlackRockS RSS

    UnQualify and Concantenate issue

    Denis Woods

      Hi,

       

      I have issue with the below script for some reason it is not showing data from the qvd's

      S1-OpenCalls.qvd and S1-ProcessExceptions.qvd ?

      It shows data from the S1-ClosedCalls.qvd (EUC) but nothe the other qvd's, any idea's why this is?

       

       

       
      
      unqualify *;
      
      Qualify *;
      unqualify DB;
      unqualify EUCalendarkey;
      unqualify EUCALLNO;
      unqualify EUCallAgeCode;
      unqualify EUCALLPK;
      unqualify EUExtractDate;
      unqualify EULoggedTodayFlag;
      EUCal:
      LOAD 
           Db as DB  ,
           CalendarKey as EUCalendarkey, 
           [Engineer ID],
           [Eng Day],
           [Eng Quarter],
           [Eng Date], 
           [Eng Week], 
           [Eng Month], 
           [Eng Year], 
           [Eng Day of Week], 
           [Std Hours],
           [Avail Day Flag], 
           HOLS, 
           UNAV, 
           DOCT, 
           SICK, 
           SITE, 
           TRAIN, 
           [Total Unavail Hours], 
           [Min Arrive Time], 
           [Max Leave Time], 
           [Worked Day Flag], 
           [Extra Day Flag], 
           [Extra Day Hours], 
           [Unavailable Days],
           [Paid Time],
           [Worked Hours],
           [First Name], 
           [Last Name], 
           [Person Type], 
           [Person Group], 
           [Person Class], 
           [Engineer OSG B], 
           [Engineer OSG C], 
           [Engineer OSG D], 
           [Person Category],
           [Eng Start Month]
      FROM
      [$(vDataPath)\S1-EngineerCalendar.qvd]
      (qvd);  
      //EuVisit table
      EUV:
      LOAD 
           VisitKey,  
           CalendarKey AS EUCalendarkey,
           [Visit No], 
           [Call No] AS EUCALLNO, 
           VisitCount,  
           [Month Year Visit], 
           [Arrival Date and Time], 
           [Arrival Date], 
           [Leave Date and Time], 
           [Leave Date], 
           [Arrive Time], 
           [Leave Time], 
           [Arrive Day], 
           [Time on Site], 
           [Travel Time], 
           [Field Time], 
           [Visit OnSite Time], 
           Mileage,
           [PGL Travel], 
           [Parts Used], 
           [Visit Fix Flag], 
           [Visit FTF Flag],
           [Std Model Time],
           [Travel Allowance] 
      FROM
      [$(vDataPath)\S1-Visits.qvd]
      (qvd);
      
      //Load EUCalls
      EUC:
      LOAD [Customer Group 2],       //COMPLETED CALLS
           [Customer Group 1], 
           [Call Type], 
           [Call Category], 
           [Call No] aS EUCALLNO, 
           [Call No PN PK],
           [Exception Code], 
           [Exception Area],
           [Fix Exception Flag], 
           [Rejected Flag], 
           [Mitigation Code], 
           [Mitigation Area],
           Mitigated, 
           [Time Changed Flag], 
           [Uncoded Flag], 
           [Coded Flag], 
           [MIT True Flag], 
           [Customer Ref], 
           [NRPM Flag], 
           [BAPM Flag], 
           [LDPM Flag], 
           [Hold Flag], 
           [MIT Hold Flag], 
           [Fix Exc Hold Flag], 
           [Hold TCNM Flag],
           [MIT Non Hold Flag], 
           [String Id], 
           Queue, 
           [Request Type], 
           Severity, 
           [Severity (C)],
           Resolution, 
           [Fix Engineer Name], 
           [First Time Fix Flag], 
           [User Def 1], 
           [Default Brand], 
           Priority, 
           [Problem Code], 
           [Logged By ID], 
           [First TST Update ID], 
           [First TST Update Date Time], 
           [First TST B4 Fail Flag], 
           [First TST Time], 
           [First TST 30 Mins from Log Flag], 
           [First TST Flag], 
           [Logged Date], 
           [Logged Date Time], 
           [Logged Hour], 
           [Contract Type], 
           [Payment ID], 
           [Model ID], 
           Model, 
           [Product Family], 
           [Chargeable Call Flag], 
           [Serial No], 
           [Actual Fix], 
           [Completed Date Time], 
           [Completed Date], 
           [Open Date Time], 
           [Target Fix], 
           [Total Visits], 
           [DS Flag],
           [DS Category],
           [Total Parts Usage], 
           [Total Parts Used], 
           [Total Parts Used Flag],
           [Total Parts Needs], 
           [Total Parts Needed], 
           [SLA Fail Date Time],
           [BAE Call Type], 
           [Reopened Flag], 
           [Site ID], 
           Branch, 
           Account, 
           [Post Code], 
           City, 
           Customer, 
           [PSG A], 
           [PSG B], 
           [PSG C], 
           [PSG D], 
           [PSG E], 
           [OSG A], 
           [OSG B], 
           [OSG C], 
           [OSG D], 
           [OSG E], 
           [Customer Group 3], 
           [Target SLA], 
           [Customer Tier], 
           [Clearance Level], 
           [Clearance Flag], 
           [Test Flag (TST Fix)], 
           [Exception Description], 
           [Mitigation Description], 
           [Exec Name], 
           [Manager Name], 
           [Onsite Time (Tm2)],
           [PDA Completed],      
           [3RD], 
           TSTQ,
           [Created ID Warranty], 
           [Period No],
           [Period No Format],
           [Warr Checked],
           [Place ID],
           [SLA Flag],
           [SLA Flag Detail]      
      FROM
      [$(vDataPath)\S1-ClosedCalls.qvd]
      (qvd);
      CONCATENATE
      LOAD [Call Type],      //OPEN Calls
           [Call Category], 
           [Call PK] as EUCALLPK, 
           [Call No] AS EUCALLNO, 
           [Call No PN PK],
           [Customer Ref], 
           Customer, 
           [String Id], 
           Queue, 
           [Action Status], 
           Severity,
           [Severity (C)], 
           Status, 
           [Logged By ID], 
           [Logged Date], 
           [2 Day Flag], 
           [5 Day Flag], 
           [7 Day Flag], 
           [14 Day Flag], 
           [Logged Date Time], 
           [Logged Time], 
           [Logged Hour], 
           [Logged OOH Flag], 
           [Logged OOH], 
           [Obliged Arrive (Appt)], 
           [Contract Type], 
           [Payment ID], 
           [Model ID], 
           Model, 
           [Product Name], 
           [Product Family], 
           [Serial No], 
           City, 
           [Post Code], 
           [PSG A], 
           [PSG B], 
           [PSG C], 
           [PSG D], 
           [Last Text Code], 
           [Last Text Date], 
           [Last Text], 
           [Last Text By], 
           [Assigned To], 
           [Assigned Person], 
           [FE ETA], 
           [FE Assignment Date Time], 
           [FE Time On Site], 
           [FE Planned Today Flag], 
           [FE ETA Expired Flag], 
           [FE ETA OK Flag], 
           [FE Not Planned], 
           [Target Fix], 
           [Total Visits], 
           [DS Flag],
           [DS Category],  
           [Total Parts Needs], 
           [Total Parts Needed], 
           [SLA Fail Date Time], 
           [SLA Fail Date Time Minus Hour], 
           [SLA Left], 
           [SLA RAG], 
           [SLA RAG Sort], 
           [SLA Flag (In SLA)], 
           [SLA Expired Flag (Out SLA)], 
           [SLA Hold Flag (Hold SLA)], 
           [String Count], 
           [BAE Call Type], 
           [PDA Current Status], 
           [PDA Last Update], 
           [PDA Current Status Order], 
           [Place ID], 
           Branch, 
           Account, 
           [Customer Group 1], 
           [Customer Group 3], 
           [Target SLA], 
           [Customer Tier], 
           [Clearance Level], 
           [Clearance Flag], 
           [Extract Date Time] as EUExtractDate
      FROM
      [$(vDataPath)\S1-OpenCalls.qvd]
      (qvd);
      
      CONCATENATE
      LOAD [Call PK] as EUCALLPK,     //Process Exceptions
           [Call Type], 
           [Call Category], 
           [Call No] aS EUCALLNO, 
           [Call No PN PK],
           Customer, 
           [Logged Date], 
           [Logged Date Time], 
           [Completed Date Time], 
           [Completed Date], 
           [Rejected Flag], 
           [Completed Flag], 
           [Fixed Flag], 
           [Fixed After Completed Flag], 
           [Contract Type], 
           [Site ID], 
           [DS Flag], 
           [DS Category], 
           [Missing Model ID], 
           [Action Status], 
           [Model ID], 
           Queue, 
           [BAE Call Type], 
           [Cross Reference], 
           [User Def 1], 
           [Customer Group 1], 
           [Customer Group 2], 
           [Customer Group 3], 
           [Target SLA], 
           [Customer Tier], 
           [Clearance Level], 
           [Clearance Flag]
      FROM
      [$(vDataPath)\S1-ProcessExceptions.qvd]
      (qvd);
      
        • UnQualify and Concantenate issue

          Hi,

           

            Can u please upload your application.

           

           

           

          Regards,

          Antony.

            • UnQualify and Concantenate issue
              Denis Woods

              Hi the script is here or is there another way to add the script?

                • UnQualify and Concantenate issue
                  Denis Woods

                  Hi,  Can you see the script now?

                    • UnQualify and Concantenate issue

                      Hi,

                       

                           please send your data it would be very easy to find the problems.

                       

                       

                      Regards,

                      Antony.

                        • UnQualify and Concantenate issue

                          i have one query , which are the tables not showing ??? can you listed those tables???

                            • UnQualify and Concantenate issue
                              Denis Woods

                              Its one query that is creating 3 tables:

                               

                              EUCal,

                              EUV,

                              EUCalls

                               

                              I am having a issue with the EUCalls, for some reason it is not showing data from the CONCATENATE qvd files from S1-OpenCalls.qvd and S1-ProcessExceptions.qvd

                                • UnQualify and Concantenate issue

                                  S1-OpenCalls.qvd and S1-ProcessExceptions.qvd both the tables merged in EUCalls table....

                                    • UnQualify and Concantenate issue
                                      Denis Woods

                                      Yes they are both merged in the EUCalls table, so not sure if the Concatemate is causing a issue?

                                        • UnQualify and Concantenate issue

                                          Have u checked in application whether its showing the data or not after reload......

                                            • UnQualify and Concantenate issue

                                              its showing or not .....

                                              • Re: UnQualify and Concantenate issue
                                                Denis Woods

                                                I have added a new sheet within my document, add a New Table Box and then selected the EUC from "Show Fields from Table" it shows fields with EUC.3RD, EUC.Account etc.. but when i scroll further down it shows fields like S1-OpenCalls.14 Day Flag and S1-ProcessExceptions.Action Status so it does not look like it is putting this information within the table EUC? And cannot see why?

                                                  • Re: UnQualify and Concantenate issue

                                                    i did not understand......

                                                      • Re: UnQualify and Concantenate issue
                                                        Denis Woods

                                                        Basically when creating a table from EUC it will show fields from EUC, S1-OpenCalls and S1-ProcessExceptions, so for some reason S1-OpenCalls and S1-ProcessExceptions are not being concatenated within the EUC table?

                                                          • Re: UnQualify and Concantenate issue

                                                            can u share your application with me.......

                                                              • Re: UnQualify and Concantenate issue
                                                                Denis Woods

                                                                I've attached a screen dump of the fields that are being listed, as you can see it will show fields for EUC. but it is not putting the fields from S1-OpenCalls within EUC, it has created a fieldname with the qvd name at the beginning. I cannot share the Document unfortunately.

                                                                  • UnQualify and Concantenate issue

                                                                    Dear,

                                                                     

                                                                     

                                                                             Its picked ur 3 tables....

                                                                    EUC table fields showing EUC(tablename.fieldname)....

                                                                    Open call table fields showing Open calls(tablename.fieldname).... Due to Qualify...

                                                                     

                                                                    i dint understand what problem your facing, in your attachment its showing both the tables data with the table name of that table.

                                                                      • UnQualify and Concantenate issue
                                                                        Denis Woods

                                                                        I don't want it to pick S1-OpenCalls and S1-ProcessExceptions they should appear within the Table EUC just like the S1-ClosedCalls has done

                                                                          • UnQualify and Concantenate issue

                                                                            remove qualify from S1-OpenCalls and S1-ProcessExceptions.....

                                                                              • UnQualify and Concantenate issue
                                                                                Denis Woods

                                                                                Where on the script do I need it to remove this from? As cannot see it? Would you be able to amend the script? Thanks

                                                                                  • UnQualify and Concantenate issue

                                                                                    Qualify *;
                                                                                    unqualify DB;
                                                                                    unqualify EUCalendarkey;
                                                                                    unqualify EUCALLNO;
                                                                                    unqualify EUCallAgeCode;
                                                                                    unqualify EUCALLPK;
                                                                                    unqualify EUExtractDate;
                                                                                    unqualify EULoggedTodayFlag;
                                                                                    EUCal:
                                                                                    LOAD
                                                                                         Db as DB  ,
                                                                                         CalendarKey as EUCalendarkey,
                                                                                         [Engineer ID],
                                                                                         [Eng Day],
                                                                                         [Eng Quarter],
                                                                                         [Eng Date],
                                                                                         [Eng Week],
                                                                                         [Eng Month],
                                                                                         [Eng Year],
                                                                                         [Eng Day of Week],
                                                                                         [Std Hours],
                                                                                         [Avail Day Flag],
                                                                                         HOLS,
                                                                                         UNAV,
                                                                                         DOCT,
                                                                                         SICK,
                                                                                         SITE,
                                                                                         TRAIN,
                                                                                         [Total Unavail Hours],
                                                                                         [Min Arrive Time],
                                                                                         [Max Leave Time],
                                                                                         [Worked Day Flag],
                                                                                         [Extra Day Flag],
                                                                                         [Extra Day Hours],
                                                                                         [Unavailable Days],
                                                                                         [Paid Time],
                                                                                         [Worked Hours],
                                                                                         [First Name],
                                                                                         [Last Name],
                                                                                         [Person Type],
                                                                                         [Person Group],
                                                                                         [Person Class],
                                                                                         [Engineer OSG B],
                                                                                         [Engineer OSG C],
                                                                                         [Engineer OSG D],
                                                                                         [Person Category],
                                                                                         [Eng Start Month]
                                                                                    FROM
                                                                                    [$(vDataPath)\S1-EngineerCalendar.qvd]
                                                                                    (qvd); 
                                                                                    //EuVisit table
                                                                                    EUV:
                                                                                    LOAD
                                                                                         VisitKey, 
                                                                                         CalendarKey AS EUCalendarkey,
                                                                                         [Visit No],
                                                                                         [Call No] AS EUCALLNO,
                                                                                         VisitCount, 
                                                                                         [Month Year Visit],
                                                                                         [Arrival Date and Time],
                                                                                         [Arrival Date],
                                                                                         [Leave Date and Time],
                                                                                         [Leave Date],
                                                                                         [Arrive Time],
                                                                                         [Leave Time],
                                                                                         [Arrive Day],
                                                                                         [Time on Site],
                                                                                         [Travel Time],
                                                                                         [Field Time],
                                                                                         [Visit OnSite Time],
                                                                                         Mileage,
                                                                                         [PGL Travel],
                                                                                         [Parts Used],
                                                                                         [Visit Fix Flag],
                                                                                         [Visit FTF Flag],
                                                                                         [Std Model Time],
                                                                                         [Travel Allowance]
                                                                                    FROM
                                                                                    [$(vDataPath)\S1-Visits.qvd]
                                                                                    (qvd);

                                                                                     

                                                                                    UNQUALIFY *;


                                                                                    //Load EUCalls
                                                                                    EUC:
                                                                                    LOAD [Customer Group 2],       //COMPLETED CALLS
                                                                                         [Customer Group 1],
                                                                                         [Call Type],
                                                                                         [Call Category],
                                                                                         [Call No] aS EUCALLNO,
                                                                                         [Call No PN PK],
                                                                                         [Exception Code],
                                                                                         [Exception Area],
                                                                                         [Fix Exception Flag],
                                                                                         [Rejected Flag],
                                                                                         [Mitigation Code],
                                                                                         [Mitigation Area],
                                                                                         Mitigated,
                                                                                         [Time Changed Flag],
                                                                                         [Uncoded Flag],
                                                                                         [Coded Flag],
                                                                                         [MIT True Flag],
                                                                                         [Customer Ref],
                                                                                         [NRPM Flag],
                                                                                         [BAPM Flag],
                                                                                         [LDPM Flag],
                                                                                         [Hold Flag],
                                                                                         [MIT Hold Flag],
                                                                                         [Fix Exc Hold Flag],
                                                                                         [Hold TCNM Flag],
                                                                                         [MIT Non Hold Flag],
                                                                                         [String Id],
                                                                                         Queue,
                                                                                         [Request Type],
                                                                                         Severity,
                                                                                         [Severity (C)],
                                                                                         Resolution,
                                                                                         [Fix Engineer Name],
                                                                                         [First Time Fix Flag],
                                                                                         [User Def 1],
                                                                                         [Default Brand],
                                                                                         Priority,
                                                                                         [Problem Code],
                                                                                         [Logged By ID],
                                                                                         [First TST Update ID],
                                                                                         [First TST Update Date Time],
                                                                                         [First TST B4 Fail Flag],
                                                                                         [First TST Time],
                                                                                         [First TST 30 Mins from Log Flag],
                                                                                         [First TST Flag],
                                                                                         [Logged Date],
                                                                                         [Logged Date Time],
                                                                                         [Logged Hour],
                                                                                         [Contract Type],
                                                                                         [Payment ID],
                                                                                         [Model ID],
                                                                                         Model,
                                                                                         [Product Family],
                                                                                         [Chargeable Call Flag],
                                                                                         [Serial No],
                                                                                         [Actual Fix],
                                                                                         [Completed Date Time],
                                                                                         [Completed Date],
                                                                                         [Open Date Time],
                                                                                         [Target Fix],
                                                                                         [Total Visits],
                                                                                         [DS Flag],
                                                                                         [DS Category],
                                                                                         [Total Parts Usage],
                                                                                         [Total Parts Used],
                                                                                         [Total Parts Used Flag],
                                                                                         [Total Parts Needs],
                                                                                         [Total Parts Needed],
                                                                                         [SLA Fail Date Time],
                                                                                         [BAE Call Type],
                                                                                         [Reopened Flag],
                                                                                         [Site ID],
                                                                                         Branch,
                                                                                         Account,
                                                                                         [Post Code],
                                                                                         City,
                                                                                         Customer,
                                                                                         [PSG A],
                                                                                         [PSG B],
                                                                                         [PSG C],
                                                                                         [PSG D],
                                                                                         [PSG E],
                                                                                         [OSG A],
                                                                                         [OSG B],
                                                                                         [OSG C],
                                                                                         [OSG D],
                                                                                         [OSG E],
                                                                                         [Customer Group 3],
                                                                                         [Target SLA],
                                                                                         [Customer Tier],
                                                                                         [Clearance Level],
                                                                                         [Clearance Flag],
                                                                                         [Test Flag (TST Fix)],
                                                                                         [Exception Description],
                                                                                         [Mitigation Description],
                                                                                         [Exec Name],
                                                                                         [Manager Name],
                                                                                         [Onsite Time (Tm2)],
                                                                                         [PDA Completed],     
                                                                                         [3RD],
                                                                                         TSTQ,
                                                                                         [Created ID Warranty],
                                                                                         [Period No],
                                                                                         [Period No Format],
                                                                                         [Warr Checked],
                                                                                         [Place ID],
                                                                                         [SLA Flag],
                                                                                         [SLA Flag Detail]     
                                                                                    FROM
                                                                                    [$(vDataPath)\S1-ClosedCalls.qvd]
                                                                                    (qvd);
                                                                                    CONCATENATE
                                                                                    LOAD [Call Type],      //OPEN Calls
                                                                                         [Call Category],
                                                                                         [Call PK] as EUCALLPK,
                                                                                         [Call No] AS EUCALLNO,
                                                                                         [Call No PN PK],
                                                                                         [Customer Ref],
                                                                                         Customer,
                                                                                         [String Id],
                                                                                         Queue,
                                                                                         [Action Status],
                                                                                         Severity,
                                                                                         [Severity (C)],
                                                                                         Status,
                                                                                         [Logged By ID],
                                                                                         [Logged Date],
                                                                                         [2 Day Flag],
                                                                                         [5 Day Flag],
                                                                                         [7 Day Flag],
                                                                                         [14 Day Flag],
                                                                                         [Logged Date Time],
                                                                                         [Logged Time],
                                                                                         [Logged Hour],
                                                                                         [Logged OOH Flag],
                                                                                         [Logged OOH],
                                                                                         [Obliged Arrive (Appt)],
                                                                                         [Contract Type],
                                                                                         [Payment ID],
                                                                                         [Model ID],
                                                                                         Model,
                                                                                         [Product Name],
                                                                                         [Product Family],
                                                                                         [Serial No],
                                                                                         City,
                                                                                         [Post Code],
                                                                                         [PSG A],
                                                                                         [PSG B],
                                                                                         [PSG C],
                                                                                         [PSG D],
                                                                                         [Last Text Code],
                                                                                         [Last Text Date],
                                                                                         [Last Text],
                                                                                         [Last Text By],
                                                                                         [Assigned To],
                                                                                         [Assigned Person],
                                                                                         [FE ETA],
                                                                                         [FE Assignment Date Time],
                                                                                         [FE Time On Site],
                                                                                         [FE Planned Today Flag],
                                                                                         [FE ETA Expired Flag],
                                                                                         [FE ETA OK Flag],
                                                                                         [FE Not Planned],
                                                                                         [Target Fix],
                                                                                         [Total Visits],
                                                                                         [DS Flag],
                                                                                         [DS Category], 
                                                                                         [Total Parts Needs],
                                                                                         [Total Parts Needed],
                                                                                         [SLA Fail Date Time],
                                                                                         [SLA Fail Date Time Minus Hour],
                                                                                         [SLA Left],
                                                                                         [SLA RAG],
                                                                                         [SLA RAG Sort],
                                                                                         [SLA Flag (In SLA)],
                                                                                         [SLA Expired Flag (Out SLA)],
                                                                                         [SLA Hold Flag (Hold SLA)],
                                                                                         [String Count],
                                                                                         [BAE Call Type],
                                                                                         [PDA Current Status],
                                                                                         [PDA Last Update],
                                                                                         [PDA Current Status Order],
                                                                                         [Place ID],
                                                                                         Branch,
                                                                                         Account,
                                                                                         [Customer Group 1],
                                                                                         [Customer Group 3],
                                                                                         [Target SLA],
                                                                                         [Customer Tier],
                                                                                         [Clearance Level],
                                                                                         [Clearance Flag],
                                                                                         [Extract Date Time] as EUExtractDate
                                                                                    FROM
                                                                                    [$(vDataPath)\S1-OpenCalls.qvd]
                                                                                    (qvd);

                                                                                    CONCATENATE
                                                                                    LOAD [Call PK] as EUCALLPK,     //Process Exceptions
                                                                                         [Call Type],
                                                                                         [Call Category],
                                                                                         [Call No] aS EUCALLNO,
                                                                                         [Call No PN PK],
                                                                                         Customer,
                                                                                         [Logged Date],
                                                                                         [Logged Date Time],
                                                                                         [Completed Date Time],
                                                                                         [Completed Date],
                                                                                         [Rejected Flag],
                                                                                         [Completed Flag],
                                                                                         [Fixed Flag],
                                                                                         [Fixed After Completed Flag],
                                                                                         [Contract Type],
                                                                                         [Site ID],
                                                                                         [DS Flag],
                                                                                         [DS Category],
                                                                                         [Missing Model ID],
                                                                                         [Action Status],
                                                                                         [Model ID],
                                                                                         Queue,
                                                                                         [BAE Call Type],
                                                                                         [Cross Reference],
                                                                                         [User Def 1],
                                                                                         [Customer Group 1],
                                                                                         [Customer Group 2],
                                                                                         [Customer Group 3],
                                                                                         [Target SLA],
                                                                                         [Customer Tier],
                                                                                         [Clearance Level],
                                                                                         [Clearance Flag]
                                                                                    FROM
                                                                                    [$(vDataPath)\S1-ProcessExceptions.qvd]
                                                                                    (qvd);