topic UnQualify and Concantenate issue in QlikView

UnQualify and Concantenate issue

denwo2010 — Mon, 25 Jul 2011 08:34:21 GMT

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

Mon, 25 Jul 2011 08:39:12 GMT

Hi,

Can u please upload your application.

Regards,

Antony.

UnQualify and Concantenate issue

denwo2010 — Mon, 25 Jul 2011 08:41:27 GMT

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

UnQualify and Concantenate issue

denwo2010 — Mon, 25 Jul 2011 09:28:50 GMT

Hi, Can you see the script now?

UnQualify and Concantenate issue

Mon, 25 Jul 2011 10:00:49 GMT

Hi,

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

Regards,

Antony.

UnQualify and Concantenate issue

Mon, 25 Jul 2011 10:03:23 GMT

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

UnQualify and Concantenate issue

denwo2010 — Mon, 25 Jul 2011 10:15:33 GMT

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

Mon, 25 Jul 2011 10:21:10 GMT

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

UnQualify and Concantenate issue

denwo2010 — Mon, 25 Jul 2011 10:23:53 GMT

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

UnQualify and Concantenate issue

Mon, 25 Jul 2011 10:26:24 GMT

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

UnQualify and Concantenate issue

Mon, 25 Jul 2011 10:38:06 GMT

its showing or not .....

Re: UnQualify and Concantenate issue

denwo2010 — Mon, 25 Jul 2011 10:46:18 GMT

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

Mon, 25 Jul 2011 10:56:51 GMT

i did not understand......

Re: UnQualify and Concantenate issue

denwo2010 — Mon, 25 Jul 2011 10:59:47 GMT

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

Mon, 25 Jul 2011 11:04:16 GMT

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

Re: UnQualify and Concantenate issue

denwo2010 — Mon, 25 Jul 2011 11:08:11 GMT

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

Mon, 25 Jul 2011 11:18:41 GMT

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

denwo2010 — Mon, 25 Jul 2011 11:36:22 GMT

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

Mon, 25 Jul 2011 11:53:07 GMT

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

UnQualify and Concantenate issue

denwo2010 — Mon, 25 Jul 2011 12:01:48 GMT

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