Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

Creation of a bucket table with given data

Hi all

I have data given in such way

USD12-Jan-1860
INR25-Jan-1815
USD14-May-1862
INR14-Jul-1816
USD01-Oct-1861
INR28-Feb-1918

 

and i want to create a table like this

USD12-Jan-1813-May-1860
INR25-Jan-18

13-Jul-18

15
USD14-May-1830-Sept-1862
INR14-Jul-1827-Feb-1916
USD01-Oct-18(PresentDay)61
INR28-Feb-19(PresentDay)18

 

Can anyone help me achieve this. 

Tags (3)
Labels (1)
2 Replies
Partner
Partner

Re: Creation of a bucket table with given data

Try below logic,

Base:
Load * inline [
Currency,Date,Value
USD,12-Jan-2018,60
INR,25-Jan-2018,15
USD,14-May-2018,62
INR,14-Jul-2018,16
USD,01-Oct-2018,61
INR,28-Feb-2019,18
];

NoConcatenate
Temp:
Load
Currency,
Date(Date#(Date,'DD-MMMM-YYYY'),'DD/MM/YYYY') as StartDate,
Value
Resident Base;
Drop Table Base;


Final:
Load
Currency,
StartDate,
if(Currency= Previous(Currency),Date(Previous(StartDate)-1),Today()) as EndDate,
Value
Resident Temp Order by Currency,StartDate desc;
Drop Table Temp;

Highlighted

Re: Creation of a bucket table with given data

What is the logic behind?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)