Question about age analysis

[zaahier_dollie]

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

[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]

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

[zaahier_dollie]

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]);