Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Change Dimensions in my table

 
 

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? 

7 Replies
Highlighted
Luminary
Luminary

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
Highlighted
Contributor
Contributor

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

 

Highlighted
Luminary
Luminary

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
Highlighted
Contributor
Contributor

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

Highlighted
Contributor
Contributor

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?

 

Highlighted
Luminary
Luminary

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
Highlighted
Contributor
Contributor

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