Change Dimensions in my table

elthause · ‎2019-09-16

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 · ‎2019-09-16

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;

Blog: WhereClause Twitter: @treysmithdev

elthause · ‎2019-09-16

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 · ‎2019-09-16

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.

Blog: WhereClause Twitter: @treysmithdev

elthause · ‎2019-09-18

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

elthause · ‎2019-09-19

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 · ‎2019-09-19

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

Blog: WhereClause Twitter: @treysmithdev

elthause · ‎2019-09-19

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

dimension

General Question

Table Transformation

table view

Table_structure