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

[OmarBenSalem]

Hi all,

my demand is rather simple,

I have an excel file :

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 :

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

[sunny_talwar · Accepted solution]

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]

Yes, that went really good !!

Thank you very much

[OmarBenSalem]

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

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]

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:

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,

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

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]

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]

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