Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

creating a single interval (min date-max date) from a list of intervals depending on a situation

Hi all,

my demand is rather simple,

I have an excel file :

Capture.PNG

as you can see, the id 1 has situation A from 10/01/2017 to 13/01/2017 then from 17/01/2017 to 18/01/2017
a situation B from 14/01/2017 to 16/01/2017

So I want to be able to limit the intervals to those listed above depending on the change of the situation:

here is what I've did:

test:

Load

id,

situation,

date(min(debut)) as MinDate,

date(max(fin)) as MaxDate

Group by id,situation;

LOAD

  id,

    debut,

    fin,

    situation

FROM [lib://source/source.xlsx]

(ooxml, embedded labels, table is Feuil1);


this was the result :


Capture.PNG


Now, the question is, how can I seperate 10/01 to 13/01 and 17/01 to 18/01 ?
Thanks !

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Table:

LOAD * INLINE [

    id, situation, debut, fin

    1, A, 10/01/2017, 10/01/2017

    1, A, 11/01/2017, 11/01/2017

    1, A, 12/01/2017, 12/01/2017

    1, A, 13/01/2017, 13/01/2017

    1, B, 14/01/2017, 14/01/2017

    1, B, 15/01/2017, 15/01/2017

    1, B, 16/01/2017, 16/01/2017

    1, A, 17/01/2017, 17/01/2017

    1, A, 18/01/2017, 18/01/2017

    2, B, 10/01/2017, 10/01/2017

    2, B, 11/01/2017, 11/01/2017

    2, A, 12/01/2017, 12/01/2017

];

TempTable:

LOAD *,

  If(id = Previous(id),

  If(situation = Previous(situation), Peek('Flag'), RangeSum(Peek('Flag'), 1)), 1) as Flag

Resident Table

Order By id, debut;

FinalTable:

LOAD id,

  situation,

  Flag,

  Date(Min(debut)) as MinDate,

  Date(Max(fin)) as MaxDate

Resident TempTable

Group By id, situation, Flag;

DROP Tables Table, TempTable;

View solution in original post

17 Replies
sunny_talwar

Try this:

Table:

LOAD * INLINE [

    id, situation, debut, fin

    1, A, 10/01/2017, 10/01/2017

    1, A, 11/01/2017, 11/01/2017

    1, A, 12/01/2017, 12/01/2017

    1, A, 13/01/2017, 13/01/2017

    1, B, 14/01/2017, 14/01/2017

    1, B, 15/01/2017, 15/01/2017

    1, B, 16/01/2017, 16/01/2017

    1, A, 17/01/2017, 17/01/2017

    1, A, 18/01/2017, 18/01/2017

    2, B, 10/01/2017, 10/01/2017

    2, B, 11/01/2017, 11/01/2017

    2, A, 12/01/2017, 12/01/2017

];

TempTable:

LOAD *,

  If(id = Previous(id),

  If(situation = Previous(situation), Peek('Flag'), RangeSum(Peek('Flag'), 1)), 1) as Flag

Resident Table

Order By id, debut;

FinalTable:

LOAD id,

  situation,

  Flag,

  Date(Min(debut)) as MinDate,

  Date(Max(fin)) as MaxDate

Resident TempTable

Group By id, situation, Flag;

DROP Tables Table, TempTable;

OmarBenSalem
Author

Yes, that went really good !!

Thank you very much

OmarBenSalem
Author

Hi Sunny,

Now that you've saved my life, can you please explain me this:

If(id = Previous(id),

  If(situation = Previous(situation), Peek('Flag'), RangeSum(Peek('Flag'), 1)), 1) as Flag



It got me a little confused. Thanks again

sunny_talwar

So for each set of combination, I am creating a unique number

Capture.PNG

So my first check is If(id = Previous(id).... If it is not, then restart the count at 1. If it is, then my second check is if situation = previous situation. If it is, then use the previous value of flag. If it is not then add 1 to the previous flag. The Order by statement plays a huge role here to make sure that the flag is created correctly.

Order by id and then by either debut or fin to make sure that all the situations = 'A' are not grouped together or ids are not all over the place.

OmarBenSalem
Author

Hi Sunny,

I'm facing some troubles and would like to ask you for help !

Here is my issue:

I have an excel file which contains some dates with sales:

Capture.PNG

What I want to do is :

we are in February, I want to compare sales in January (month-1) and in Dec 2016 (last month-2)

if sales in january are better than dec 2016, then we're doing fine (flag ='good', else 'bad').

I've elaborated somthing which works in the same year, but the problem is when we're in Feb or Jan and we have to compare with the last year:

here is what I've done :

t:

load *,(month(date)) as Month,year(date) as Year ;

LOAD

    id,

    "date",

    sales

FROM [lib://source/etat.xlsx]

(ooxml, embedded labels, table is Feuil1);

left Join(t)

load Year, LastValue(Month)-1 as LastMonth

Resident t

Group by Year;

left Join(t)

load Year, LastValue(Month)-2 as "LastMonth-1"

Resident t

Group by Year;

// left Join(t)

// load Year, if( Month='févr','déc',LastValue(Month)-2) as "LastMonth-1"

// Resident t

// Group by Year;

a:

load sum(sales) as salesLast, Month, Year Resident t Where LastMonth="Month" Group By Year,"Month";

Concatenate

load sum(sales) as "salesLast-1", Month, Year Resident t Where "LastMonth-1"="Month" Group By Year,"Month";

drop table t;

load * Where row=1;

load *, RecNo() as row ;

load ("Month"), "Year" ,

If(salesLast>= "salesLast-1",'En hausse','En Baisse') as etat;

final:

Load * Resident a Order by Month desc ;

Drop table a;

Now, if in my source, the last month is Mars, so I'll be comparing Feb and Jan of the same year, it will be ok; and the result will be correct; but when the last month is fev or january, it will be incorrect.

How could I correct this?

Thank you very much !


ps: if in my source ,the last month was March,

Capture.PNG

I will have the result I want with the same script:

Capture.PNG

which means, that sales in févr have decreased in comparison with sales in January.

Hope you'll be my savior one again !

sunny_talwar

Use MonthYear field instead of just month

t:

LOAD *,

    MonthName(date) as MonthYear,

    year(date) as Year;

LOAD id,

    "date",

    sales

FROM [lib://source/etat.xlsx]

(ooxml, embedded labels, table is Feuil1);

Left Join(t)

LAOD Year,

    AddMonths(LastValue(MonthYear), -1) as LastMonth

Resident t

Group by Year;

Left Join(t)

LOAD Year,

    AddMonths(LastValue(MonthYear), -2) as "LastMonth-1"

Resident t

Group by Year;

I am not sure how exactly LastValue is working here for you, but this should be able to solve your change of year problems

OmarBenSalem
Author

Thank you Sunny for the quick answer but No, That didn't solve the problem .. I'm really confused !

I wanted to include if statement, to force lastValue-1 and -2 in case the months are févr. and janv. but I keep having errors message:

this part:

// left Join(t)

// load Year, if( Month='févr','déc',LastValue(Month)-2) as "LastMonth-1"

// Resident t

// Group by Year;

sunny_talwar

May be this

Left Join(t)

LOAD Year,

     LastValue(if(Month='févr', 'déc', Month-2)) as "LastMonth-1"

Resident t

Group by Year;

OmarBenSalem
Author

Yes, the error has dissepeared, but that didn't solve the problem