Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
elthause
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
treysmithdev
Luminary Alumni
Luminary Alumni

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

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
Luminary Alumni
Luminary Alumni

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

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

elthause
Contributor
Contributor
Author

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
Luminary Alumni
Luminary Alumni

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

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