Data model

[Anonymous]

Hi All,

this is my Dataset

Data:

LOAD * Inline [

DefectID, Application, OpenDate, CloseDate, Status

001,App1,01/01/2016,01/01/2016,Closed

002,App2,01/01/2016,01/01/2016,Closed

003,App2,01/01/2016,02/01/2016,Closed

004,App3,01/01/2016,03/01/2016,Closed

005,App3,01/01/2016,,Open

006,App3,02/01/2016,02/01/2016,Closed

007,App3,02/01/2016,02/01/2016,Closed

008,App4,02/01/2016,02/01/2016,Closed

009,App1,03/01/2016,03/01/2016,Closed

010,App1,03/01/2016,,Open

011,App3,04/01/2016,04/01/2016,Closed

012,App4,04/01/2016,04/01/2016,Closed

013,App3,04/01/2016,04/01/2016,Closed

014,App2,04/01/2016,05/01/2016,Closed

015,App2,05/01/2016,05/01/2016,Closed

016,App1,05/01/2016,05/01/2016,Closed

017,App1,05/01/2016,05/01/2016,Closed

018,App3,05/01/2016,05/01/2016,Closed

019,App4,05/01/2016,05/01/2016,Closed

020,App1,05/01/2016,,Open

];

i want to build another table as shown below from above table

DATE,         OPEN, CLOSE, REMAINING, TOTAL

01/01/2016       5      2        3         3

02/01/2016      3      4       -1         2

03/01/2016      2      2        0         2

04/01/2016      4      3        1         3

05/01/2016      6      6        0         3

OPEN- no of defect open on that particular date

CLOSE- no of defect Close on that particular date

REMAINING - OPEN - CLOSE

TOTAL - calculation as shown below

      01 /01/ 2016 = 5 - 2 = 3

      02/ 01/ 2016 = 3 - 4 = -1 + 3 (above value) = 2

      03/01/2016   =  2 - 2 = 0 + 2 = 2

      04/01/2016 = 4 - 3= 1 + 2 = 3 soon on

[Anonymous]

what you want exactly

[Anonymous]

i wanted to build second table using above table @ script level

[Kushal_Chawda]

check this

Data:

LOAD DefectID, Application, OpenDate as Date,Status,'Open' as Flag;

LOAD * Inline [

DefectID, Application, OpenDate, CloseDate, Status

001,App1,01/01/2016,01/01/2016,Closed

002,App2,01/01/2016,01/01/2016,Closed

003,App2,01/01/2016,02/01/2016,Closed

004,App3,01/01/2016,03/01/2016,Closed

005,App3,01/01/2016,,Open

006,App3,02/01/2016,02/01/2016,Closed

007,App3,02/01/2016,02/01/2016,Closed

008,App4,02/01/2016,02/01/2016,Closed

009,App1,03/01/2016,03/01/2016,Closed

010,App1,03/01/2016,,Open

011,App3,04/01/2016,04/01/2016,Closed

012,App4,04/01/2016,04/01/2016,Closed

013,App3,04/01/2016,04/01/2016,Closed

014,App2,04/01/2016,05/01/2016,Closed

015,App2,05/01/2016,05/01/2016,Closed

016,App1,05/01/2016,05/01/2016,Closed

017,App1,05/01/2016,05/01/2016,Closed

018,App3,05/01/2016,05/01/2016,Closed

019,App4,05/01/2016,05/01/2016,Closed

020,App1,05/01/2016,,Open

];

Concatenate

LOAD DefectID, Application, CloseDate as Date,Status,'Close' as Flag;

LOAD * Inline [

DefectID, Application, OpenDate, CloseDate, Status

001,App1,01/01/2016,01/01/2016,Closed

002,App2,01/01/2016,01/01/2016,Closed

003,App2,01/01/2016,02/01/2016,Closed

004,App3,01/01/2016,03/01/2016,Closed

005,App3,01/01/2016,,Open

006,App3,02/01/2016,02/01/2016,Closed

007,App3,02/01/2016,02/01/2016,Closed

008,App4,02/01/2016,02/01/2016,Closed

009,App1,03/01/2016,03/01/2016,Closed

010,App1,03/01/2016,,Open

011,App3,04/01/2016,04/01/2016,Closed

012,App4,04/01/2016,04/01/2016,Closed

013,App3,04/01/2016,04/01/2016,Closed

014,App2,04/01/2016,05/01/2016,Closed

015,App2,05/01/2016,05/01/2016,Closed

016,App1,05/01/2016,05/01/2016,Closed

017,App1,05/01/2016,05/01/2016,Closed

018,App3,05/01/2016,05/01/2016,Closed

019,App4,05/01/2016,05/01/2016,Closed

020,App1,05/01/2016,,Open

];

New:

NoConcatenate

LOAD *

Resident Data

where len(trim(Date))>0;

DROP Table Data;

[Anonymous]

count({<opendate>}opendate) like this way you can count renaming feilds

[Kushal_Chawda]

at script level

Data:

LOAD DefectID, Application, OpenDate as Date,Status,'Open' as Flag;

LOAD * Inline [

DefectID, Application, OpenDate, CloseDate, Status

001,App1,01/01/2016,01/01/2016,Closed

002,App2,01/01/2016,01/01/2016,Closed

003,App2,01/01/2016,02/01/2016,Closed

004,App3,01/01/2016,03/01/2016,Closed

005,App3,01/01/2016,,Open

006,App3,02/01/2016,02/01/2016,Closed

007,App3,02/01/2016,02/01/2016,Closed

008,App4,02/01/2016,02/01/2016,Closed

009,App1,03/01/2016,03/01/2016,Closed

010,App1,03/01/2016,,Open

011,App3,04/01/2016,04/01/2016,Closed

012,App4,04/01/2016,04/01/2016,Closed

013,App3,04/01/2016,04/01/2016,Closed

014,App2,04/01/2016,05/01/2016,Closed

015,App2,05/01/2016,05/01/2016,Closed

016,App1,05/01/2016,05/01/2016,Closed

017,App1,05/01/2016,05/01/2016,Closed

018,App3,05/01/2016,05/01/2016,Closed

019,App4,05/01/2016,05/01/2016,Closed

020,App1,05/01/2016,,Open

];

Concatenate

Data:

LOAD DefectID, Application, CloseDate as Date,Status,'Close' as Flag;

LOAD * Inline [

DefectID, Application, OpenDate, CloseDate, Status

001,App1,01/01/2016,01/01/2016,Closed

002,App2,01/01/2016,01/01/2016,Closed

003,App2,01/01/2016,02/01/2016,Closed

004,App3,01/01/2016,03/01/2016,Closed

005,App3,01/01/2016,,Open

006,App3,02/01/2016,02/01/2016,Closed

007,App3,02/01/2016,02/01/2016,Closed

008,App4,02/01/2016,02/01/2016,Closed

009,App1,03/01/2016,03/01/2016,Closed

010,App1,03/01/2016,,Open

011,App3,04/01/2016,04/01/2016,Closed

012,App4,04/01/2016,04/01/2016,Closed

013,App3,04/01/2016,04/01/2016,Closed

014,App2,04/01/2016,05/01/2016,Closed

015,App2,05/01/2016,05/01/2016,Closed

016,App1,05/01/2016,05/01/2016,Closed

017,App1,05/01/2016,05/01/2016,Closed

018,App3,05/01/2016,05/01/2016,Closed

019,App4,05/01/2016,05/01/2016,Closed

020,App1,05/01/2016,,Open

];

New:

NoConcatenate

LOAD *

Resident Data

where len(trim(Date))>0;

DROP Table Data;

Left Join(New)

LOAD *,

    if(RowNo()=1,REMAINING, rangesum(Peek('TOTAL'),REMAINING)) as TOTAL;

LOAD *,

    OPEN- CLOSE as REMAINING;

LOAD Date,

    Count(if(Flag='Open',Date)) as OPEN,

    Count(if(Flag='Close',Date)) as CLOSE

Resident New

Group by Date

Order by Date;

NOTE : USE DISTINCT in ALL EXPRESSION

If you dont want to use distinct in expression instead of left join give the new table name so it will be linked to original table

[ankit777 · Accepted solution]

Hi

Please use he below script

Data:
LOAD * Inline [
DefectID, Application, OpenDate, CloseDate, Status
001,App1,01/01/2016,01/01/2016,Closed
002,App2,01/01/2016,01/01/2016,Closed
003,App2,01/01/2016,02/01/2016,Closed
004,App3,01/01/2016,03/01/2016,Closed
005,App3,01/01/2016,,Open
006,App3,02/01/2016,02/01/2016,Closed
007,App3,02/01/2016,02/01/2016,Closed
008,App4,02/01/2016,02/01/2016,Closed
009,App1,03/01/2016,03/01/2016,Closed
010,App1,03/01/2016,,Open
011,App3,04/01/2016,04/01/2016,Closed
012,App4,04/01/2016,04/01/2016,Closed
013,App3,04/01/2016,04/01/2016,Closed
014,App2,04/01/2016,05/01/2016,Closed
015,App2,05/01/2016,05/01/2016,Closed
016,App1,05/01/2016,05/01/2016,Closed
017,App1,05/01/2016,05/01/2016,Closed
018,App3,05/01/2016,05/01/2016,Closed
019,App4,05/01/2016,05/01/2016,Closed
020,App1,05/01/2016,,Open
];

Result:
LOAD
CloseDate as Date,
Count(DefectID) as Closed
Resident Data
where Status = 'Closed'
Group by CloseDate;

left join
LOAD Alt(Open,0) as Open,Date;
LOAD
OpenDate as Date,
Count(OpenDate) as Open
Resident Data
//where Status = 'Open'
Group by OpenDate;

LOAD Date
,Open
,Closed
,Open-Closed as Remaining
,RangeSum(Open-Closed,peek(Total)) as Total
Resident Result;

DROP Table Result;