calculate failure rate

[elthause]

Hello,

I want to calculate the failure rate of my products.

My data are like this:

serial nr              sell date           return date 

112233                 1/1/2012         -

112233                  -                        1/1/2015

112244                  1/1/2013        -

112255                  1/5/2013        -

112266                  1/1/2014        -

112266                  -                        1/1/2016

and I want to make them be like this:

serial nr              sell date           return date           days( return date - sell date)

112233                 1/1/2012        1/1/2015                  1095

112244                  1/1/2013        -                                   -

112255                  1/5/2013        -                                   -

112266                  1/1/2014       1/1/2016                   730

What can I do to have this view? 

[StarinieriG]

Hi,

try something like this:

LOAD *,
[return date]-[sell date] as days;
LOAD
[serial nr],
Date(If(Sum([sell date])=0,Null(),Sum([sell date]))) as [sell date],
Date(If(Sum([return date])=0,Null(),Sum([return date]))) as [return date]
Group By
[serial nr]
;

[elthause]

Hello StarinieriG

Thank you for your help.

I have added your code like this: 

LOAD *,
[data rientro]-[data vendita] as TTF;
Load *,
[data rientro 2]-[data rientro] as TBF;

LOAD
[matricola],
Date(If(Sum([data vendita])=0,Null(),Sum([data vendita]))) as [data vendita],
Date(If(Sum([data rientro])=0,Null(),Min([data rientro]))) as [data rientro],
Date(If(Sum([data rientro])=0,Null(),Max([data rientro]))) as [data rientro 2]
Group By
[matricola]
;

Unqualify *;

[Foglio1$]:
LOAD
[matricola],
[causale],
Date([data vendita] ) AS [data vendita],
Date([data rientro] ) AS [data rientro]
FROM [lib://Desktop/toni.xls]
(biff, embedded labels, table is Foglio1$);

The only problem I face is that the  [causale] column is not uploaded.  Could you help me on this? 

[StarinieriG]

It's because here :

LOAD
[matricola],
Date(If(Sum([data vendita])=0,Null(),Sum([data vendita]))) as [data vendita],
Date(If(Sum([data rientro])=0,Null(),Min([data rientro]))) as [data rientro],
Date(If(Sum([data rientro])=0,Null(),Max([data rientro]))) as [data rientro 2]
Group By
[matricola]
;

you are not loading causale.

If it is a field always the same for each row, you could add it like this:

LOAD *,
[data rientro]-[data vendita] as TTF,
[data rientro 2]-[data rientro] as TBF;

LOAD
[matricola],

causale,
Date(If(Sum([data vendita])=0,Null(),Sum([data vendita]))) as [data vendita],
Date(If(Sum([data rientro])=0,Null(),Min([data rientro]))) as [data rientro],
Date(If(Sum([data rientro])=0,Null(),Max([data rientro]))) as [data rientro 2]
Group By
[matricola],

causale
;

Unqualify *;

[Foglio1$]:
LOAD
[matricola],
[causale],
Date([data vendita] ) AS [data vendita],
Date([data rientro] ) AS [data rientro]
FROM [lib://Desktop/toni.xls]
(biff, embedded labels, table is Foglio1$);

If it could be null in some rows and you have to take the value, you could try like this:

LOAD *,
[data rientro]-[data vendita] as TTF,
[data rientro 2]-[data rientro] as TBF;

LOAD
[matricola],

MaxString(causale) as causale, /* or maybe if it is a number: If(Sum([causale])=0,Null(),Max([causale]) )*/
Date(If(Sum([data vendita])=0,Null(),Sum([data vendita]))) as [data vendita],
Date(If(Sum([data rientro])=0,Null(),Min([data rientro]))) as [data rientro],
Date(If(Sum([data rientro])=0,Null(),Max([data rientro]))) as [data rientro 2]
Group By
[matricola]
;

Unqualify *;

[Foglio1$]:
LOAD
[matricola],
[causale],
Date([data vendita] ) AS [data vendita],
Date([data rientro] ) AS [data rientro]
FROM [lib://Desktop/toni.xls]
(biff, embedded labels, table is Foglio1$);

[elthause]

Hello StarinieriG,

What you have suggested me works fine but I think I missed to write down the last column on my data and how it is created.

serial nr              sell date           return date       Causale

112233                 1/1/2012         -                                     060

112233                  -                        1/1/2015                     032

112244                  1/1/2013        -                                     060

112255                  1/5/2013        -                                     060

112266                  1/1/2014        -                                     060

112266                  -                        1/1/2016                     033

For this reason when I load the "Causale" column I lose the view that I wan and the "TTF" and the "TBF" columns return to be 0.