Table split last period

[agaumet]

Hi all,

I'm relatively new to Qlik sense editor and am trying to achieve the following: I have a table that contains data from client over several period. I would like to create a new table using the loading script where table1 = all data and newtable = data only for last period (month)

table1:

Client ID	Client Name	Country	Industry	Period
001	ABC	FR	Manufacturing	31/10/2020
002	DEF	CH	Capital Market	31/10/2020
003	GHI	UK	Capital Market	31/10/2020
004	JKL	US	Retail	31/10/2020
005	MNO	IN	Retail	31/10/2020
001	ABC	FR	Manufacturing	30/11/2020
002	DEF	CH	Capital Market	30/11/2020
003	GHI	UK	Capital Market	30/11/2020
004	JKL	US	Retail	30/11/2020
005	MNO	IN	Retail	30/11/2020

newtable:

Client ID	Client Name	Country	Industry	Period
001	ABC	FR	Manufacturing	30/11/2020
002	DEF	CH	Capital Market	30/11/2020
003	GHI	UK	Capital Market	30/11/2020
004	JKL	US	Retail	30/11/2020
005	MNO	IN	Retail	30/11/2020

Any suggestions on how to achieve that ? Thanks in advance for the help !

Best,

Alex

[MayilVahanan · Accepted solution]

HI @agaumet 

Try like below

T:
LOAD [Client ID],
[Client Name],
Country,
Industry,
Period
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Table-split-last-period/td-p/1761858]
(html, codepage is 1252, embedded labels, table is @1);

Inner Join(T)
Load [Client ID], Max(Period) as Period Resident T group by [Client ID];

[agaumet]

Hi @MayilVahanan ,

Works like a charm thanks ! however there's something I did not anticipate. Some client ID are present in prior period (i.e 31/10/2020) and not the last (i.e30/11/2020). I would like to keep only the one for the last period.

Any ideas how to achieve that ?

Many thanks,

Alex

[MayilVahanan]

Hi @agaumet 

In that case, inner join with only the Max(Date) . Hope it helps.

Inner Join(T)
Load Max(Period) as Period Resident T;