Change Dimensions in my table

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

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

[treysmithdev]

If you want to do it on the front end you can make a table using one dimension and three measures:

Dim - [serial nr]

Msr - Date(Max([sell date]))

Msr - Date(Max([return date]))

Msr - Num(Max([return date]) - Max([sell date]))

If you want to do it in the script you can do something like this:

Return_map:
Mapping Load
    [serial nr],
    [return date]
From
    [%source%]
Where
    Len([return date]) > 0;

Data:
Load
    [serial nr],
    [sell date],
    ApplyMap('Return_map',[serial nr],Null()) as [return date]
From
    [%source%]
Where
    Len([sell date]) > 0;

[elthause]

Hello Trey,

Thank you for you help.

The solutions on the front end works fine. 

Instead the script solution gives me some errors probably due to the fact that I don't know exactly where to ad that code 🙂

The error is that after the first Where condition it says it is expecting smth like this:

FROM [%source%]
(biff, embedded labels, table is Foglio1$);

[treysmithdev]

Yeah, you would need to change %source% and potentially other pieces of the script with the appropriate data source info. Whether that is a file, resident load, or sql query.

[elthause]

Yes, sorry I have done that but the error is still the same . At the first   Where  condition. 

[elthause]

Hello Trey,

This is my script,

Unqualify *;

LIB CONNECT TO [FaultReport];

[Vista_QClick_Vendite]:
LOAD
[cod_art],
[des_articolo],
[cod_matricola],
[Prodotto],
[Prodotto]*0&60 AS [cod_caumag];
SQL SELECT "cod_art",
"des_articolo",
"cod_matricola",
"Prodotto"
FROM "VERTICALI_Bright"."dbo"."Vista_QClick_Vendite";

CONCATENATE ([Vista_QClick_Vendite]) SQL SELECT "cod_matricola",
"cod_caumag",
"dat_doc",
"cod_art",
"des_articolo"
FROM "VERTICALI_Bright"."dbo"."Vista_QClick_Rientri";

[Vista_QClick_Vendite_180522aa-060b-66e3-0eb4-603ff6ef]:

LOAD *,
[Data Rientro]-[Data Vendita] as TTF;
Load *,
[Data Rientro 2]-[Data Vendita] as TBF;
LOAD
[cod_matricola],
Date(If(Sum([Data Vendita])=0,Null(),Min([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
[cod_matricola]
;
LOAD
[cod_art],
[des_articolo],
[cod_matricola],
[Prodotto],
[cod_caumag],
[dat_doc],
Year([dat_doc])&Year([Prodotto]) AS [Data],
date(if([cod_caumag]='060',[Prodotto],'')) AS [Data Vendita],
date(if([cod_caumag]='032',[dat_doc],'')) AS [Data Rientro]


RESIDENT [Vista_QClick_Vendite];

DROP TABLE [Vista_QClick_Vendite];
RENAME TABLE [Vista_QClick_Vendite_180522aa-060b-66e3-0eb4-603ff6ef] to [Vista_QClick_Vendite];

Now my problem is that in this way  my table has only :

cod_matricola, Data Vendita, Data Rientro, Data Rientro 2, TTF and TBF columns.

I got no errors but the columns :

cod_art, des_articolo, Prodotto, cod_caumag, dat_doc  are not loaded. 

I know that Is because of the many Load statements but I  don't know how to have this all loaded.

Could you help?

[treysmithdev]

You are losing the fields at this step:

LOAD
[cod_matricola],
Date(If(Sum([Data Vendita])=0,Null(),Min([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
[cod_matricola]
;

You'll need to do:

[Vista_QClick_Vendite_180522aa-060b-66e3-0eb4-603ff6ef]:
LOAD 
    *,
    [Data Rientro]-[Data Vendita] as TTF;
Load 
    *,
    [Data Rientro 2]-[Data Vendita] as TBF;
LOAD
    [cod_art],
    [des_articolo],
    [cod_matricola],
    [Prodotto],
    [cod_caumag],
    [dat_doc],
    Date(If(Sum([Data Vendita])=0,Null(),Min([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
    [cod_art],
    [des_articolo],
    [cod_matricola],
    [Prodotto],
    [cod_caumag],
    [dat_doc],
;
LOAD
    [cod_art],
    [des_articolo],
    [cod_matricola],
    [Prodotto],
    [cod_caumag],
    [dat_doc],
    Year([dat_doc])&Year([Prodotto]) AS [Data],
    date(if([cod_caumag]='060',[Prodotto],'')) AS [Data Vendita],
    date(if([cod_caumag]='032',[dat_doc],'')) AS [Data Rientro]
RESIDENT 
    [Vista_QClick_Vendite];

[elthause]

Hello Trey,

When I do it like you say I lose the right visualization order of the data and the data of TTF column and TBF column are 0.

Here is all the script. Maybe this is more helpful for you to understand where is my mistake.

Unqualify *;

LIB CONNECT TO [FaultReport];

[Vista_QClick_Vendite]:
LOAD
[cod_art],
[des_articolo],
[cod_matricola],
[Prodotto],
[Prodotto]*0&60 AS [cod_caumag];
SQL SELECT "cod_art",
"des_articolo",
"cod_matricola",
"Prodotto"
FROM "VERTICALI_Bright"."dbo"."Vista_QClick_Vendite";

CONCATENATE ([Vista_QClick_Vendite]) SQL SELECT "cod_matricola",
"cod_caumag",
"dat_doc",
"cod_art",
"des_articolo"
FROM "VERTICALI_Bright"."dbo"."Vista_QClick_Rientri";

[Vista_QClick_Vendite_180522aa-060b-66e3-0eb4-603ff6ef]:
LOAD *,
[Data Rientro]-[Data Vendita] as TTF;
Load *,
[Data Rientro 2]-[Data Vendita] as TBF;

LOAD
[cod_art],
[des_articolo],
[cod_matricola],
[Prodotto],
[cod_caumag],
[dat_doc],
[Data],
Date(If(Sum([Data Vendita])=0,Null(),Min([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
[cod_art],
[des_articolo],
[cod_matricola],
[Prodotto],
[cod_caumag],
[dat_doc],
[Data],
;

LOAD
[cod_art],
[des_articolo],
[cod_matricola],
[Prodotto],
[cod_caumag],
[dat_doc],
Year([dat_doc])&Year([Prodotto]) AS [Data],
date(if([cod_caumag]='060',[Prodotto],'')) AS [Data Vendita],
date(if([cod_caumag]='032',[dat_doc],'')) AS [Data Rientro]
RESIDENT [Vista_QClick_Vendite];

DROP TABLE [Vista_QClick_Vendite];
RENAME TABLE [Vista_QClick_Vendite_180522aa-060b-66e3-0eb4-603ff6ef] to [Vista_QClick_Vendite];

Like this I have 12 columns but TTF and TBF values are 0 because the date data in [Data Vendita] and [Data Rientro]  are as the first post,

cod_matricola                 [Data Vendita]                [Data Rientro]

112233                                   1/1/2012                                 -

112233                                               -                                1/1/2015

112244                                     1/1/2013                                -

112255                                     1/5/2013                                -

112266                                     1/1/2014                               -

112266                                                -                               1/1/2016