Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
zaahier_dollie
Contributor II
Contributor II

Question about age analysis

I am using the following to do age analysis:


OPEN_JOBS_AGEING:
LOAD *,
if(DIS>=0 and DIS<3,Dual('0-2 Days',10),
if(DIS>=3 and DIS<8,Dual('3-7 Days',20),
if(DIS>=8 and DIS<15,Dual('8-14 Days',30),
if(DIS>=15 and DIS<31,Dual('15-30 Days',40),
if(DIS>=31 and DIS<46,Dual('31-45 Days',50),
Dual('>46 Days',60)))))) as Ageing;

OPEN_JOBS:

LOAD [Job Number], [Current Status], [In-store], DIS
FROM [Flat Job Report.xlsx] (ooxml, embedded labels, table is [Dispatched Jobs]);

CLOSED_JOBS:

LOAD [Job Number], [Current Status], DIS, [Device Repaired Fin Month]

FROM [Jobs Closed.xlsx] (ooxml, embedded labels, table is [Job Closed]);

Problem I have though is... it only applies the ageing to OPEN JOBS and not CLOSED JOBS.

Any idea why this occurs?

thanks

3 Replies
Digvijay_Singh

Can you share your data model image, looks like something wrong in associating your tables. Also I suspect you have some synthetic keys in your data model.

shiveshsingh
Master
Master

Can you share your Data model and app? How these fields are associated?

zaahier_dollie
Contributor II
Contributor II
Author

How do I attach the qvw file?

This is basically it:

STORES:
LOAD [Bookin Store], [Store Number], [Store Name], [Store Type]
FROM (ooxml, embedded labels, table is Stores);


CLOSED_JOBS:
LOAD [Job Number], [Current Status], DIS, Brand, Model, [Model Code], [Product Code], [Applicable Month], [Cust Job Number], Technician, Customer, [Customer Email], [Customer Tel], [Customer Type], [Bookin Store], [Bookin Date], DSB, [Dispatch Store], [Dispatch Date], [Repair Date], [Collected Date], [Closed Date], [Awaiting Parts Date], [In QC Date], [QC Passed Date], [Fault Code], [Fault Description], IMEI, [IMEI Out], [Serial Number], [Serial Number Out], Swap, BER, [Is Warranty], [Is Rework], RNR, [Is Transferred], [Is Insurance Claim], [Quote Accepted], [Manuf Date], [Quote Amount], [Quote Response], [Sent To ERC], [First Quote Date], [Last Quote Response Date], [ERT Type], [ERT SubType], [Total TAT (hrs)], [Total TAT Excl Quote (hrs)], [In Warranty Repair], [In Warranty Amount], [Out Warranty Repair], [Out Warranty Amount], [Workshop Warranty Repair], [Workshop Warranty Amount], [Invoice Number 1], [Invoice Date 1], [Invoice Amount 1], [Invoice Number 2], [Invoice Date 2],
     [Invoice Amount 2], [Invoice Number 3], [Invoice Date 3], [Invoice Amount 3], [Device Repaired Fin Month]
FROM (ooxml, embedded labels, table is [Job Closed]);


JOBS_AGEING:
LOAD *,
if(DIS>=0 and DIS<3,Dual('0-2 Days',10),
if(DIS>=3 and DIS<8,Dual('3-7 Days',20),
if(DIS>=8 and DIS<15,Dual('8-14 Days',30),
if(DIS>=15 and DIS<31,Dual('15-30 Days',40),
if(DIS>=31 and DIS<46,Dual('31-45 Days',50),
Dual('>=46 Days',60)))))) as Ageing;


LOAD [Job Number], [Current Status], [In-store], DIS, Brand, Model, [Model Code], [Product Code], [Applicable Month], [Cust Job Number], Technician, Customer, [Customer Email], [Customer Tel], [Customer Type], [Bookin Store], [Bookin Date], DSB, [Dispatch Store], [Dispatch Date], [Repair Date], [Collected Date], [Closed Date], [Awaiting Parts Date], [In QC Date], [QC Passed Date], [Fault Code], [Fault Description], IMEI, [IMEI Out], [Serial Number], [Serial Number Out], Swap, BER, [Is Warranty], [Is Rework], RNR, [Is Transferred], [Is Insurance Claim], [Quote Accepted], [Manuf Date], [Quote Amount], [Quote Response], [Sent To ERC], [First Quote Date], [Last Quote Response Date], [ERT Type], [ERT SubType], [Total TAT (hrs)], [Total TAT Excl Quote (hrs)], [In Warranty Repair], [In Warranty Amount], [Out Warranty Repair], [Out Warranty Amount], [Workshop Warranty Repair], [Workshop Warranty Amount], [Invoice Number 1], [Invoice Date 1], [Invoice Amount 1], [Invoice Number 2],
     [Invoice Date 2], [Invoice Amount 2], [Invoice Number 3], [Invoice Date 3], [Invoice Amount 3]
FROM (ooxml, embedded labels, table is [Dispatched Jobs]);