Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

victoriagoncalv
New Contributor II

Help

Good Morning,

I am a new user in Qlik Sense, and I need urgent help.

I need to generate a table, through another table already loaded.

I have the fields:

Name (indicates the name of the person), day, days of production (where 0 indicates that the person did not work and 1 who worked), and the group.

In the table I have to generate, I needed to loop so that every time I changed the name or group or when the person did not work (which is indicated by the number 0) it took the start date and end date of that period.

I will try to exemplify generically, because there are many my data

I have a table similar to this loaded:

  

NomeDiaDias de ProduçãoGrupo
João12/12/20171Grupo 1
João12/12/20170-
João13/12/20171Grupo 1
João13/12/20171Grupo 1
João14/12/20171Grupo 2
Maria12/12/20170-
Maria12/12/20171Grupo 1
Maria14/12/20171Grupo 1
Maria14/12/20171Grupo 2
Maria14/12/20170-
Maria15/12/20171Grupo 1
Ana15/12/20171Grupo 1
Ana16/12/20171Grupo 1
Ana17/12/20170-
Ana18/12/20171Grupo 2

And through it had to generate an equal th

    

NomeData InícioData FimGrupo
João12/12/201712/12/2017Grupo 1
João13/12/204713/12/2017Grupo 1
João14/12/201714/12/2017Grupo 2
Maria12/12/201714/12/2017Grupo 1
Maria14/12/201714/12/2017Grupo 2
Maria15/12/201715/12/2017Grupo 1
Ana15/12/201716/12/2017Grupo 1
Ana18/12/201718/12/2017Grupo 2

I've already researched, but I still have not found anything completely to help me.

And I'm in dire need, if anyone could help me, I'd be grateful.

1 Reply
MVP
MVP

Re: Help

May be this

Table:

LOAD *,

If(Nome = Previous(Nome), If([Dias de Produção] = Previous([Dias de Produção]), Peek('Num'), RangeSum(Peek('Num'), 1)), 1) as Num;

LOAD * INLINE [

    Nome, Dia, Dias de Produção, Grupo

    João, 12/12/2017, 1, Grupo 1

    João, 12/12/2017, 0, -

    João, 13/12/2017, 1, Grupo 1

    João, 13/12/2017, 1, Grupo 1

    João, 14/12/2017, 1, Grupo 2

    Maria, 12/12/2017, 0, -

    Maria, 12/12/2017, 1, Grupo 1

    Maria, 14/12/2017, 1, Grupo 1

    Maria, 14/12/2017, 1, Grupo 2

    Maria, 14/12/2017, 0, -

    Maria, 15/12/2017, 1, Grupo 1

    Ana, 15/12/2017, 1, Grupo 1

    Ana, 16/12/2017, 1, Grupo 1

    Ana, 17/12/2017, 0, -

    Ana, 18/12/2017, 1, Grupo 2

];


FinalTable:

LOAD Nome,

Num,

Grupo,

Date(Min(Dia)) as [Data Início],

Date(Max(Dia)) as [Data Fim]

Resident Table

Where [Dias de Produção] = 1

Group By Nome, Num, Grupo;


DROP Table Table;


Capture.PNG