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 !

17 Replies
sunny_talwar

I think you might be looking for a solution like this:

The As-Of Table

OmarBenSalem
Author

Sorry to keep bothering you, but I'm in charge of a Big project and I'm getting really confused...

I can't find out a way to overcome this issue!

I have a question though,

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

why is this keeps on showing errors?

sunny_talwar

I just saw an attached Excel, what is the output you expect to see from the attached Excel? A new Column with Good/Bad? and you care about the Month of date?

OmarBenSalem
Author

You're one of a kind sunny ))

In fact, I have dates with sales, I want to be able to track the sales state of our customers.

Wich means, if a customer A, have has sales in Jan=500 ,Feb=800 (and we're currently in Mars)

I want to generate a table, which contains 3 columns:

customer | Month | Year |State

A             | Feb     | 2017 | Good (since sales for feb> sales for jan)

this table will be concatenated each time with the same piece of information for every year to guard a history.

Hope this is clear?

Thanks

sunny_talwar

Hahahaha clear but I still have questions

1) Is id = customer here?

2) You have date in your Excel, do you Sum(sales) to get sales for the Month?

3) Your final table will have customer, month, year, state and may be sum(sales)? rest all information isn't needed?

OmarBenSalem
Author

1) Yes, id = customer

2) here is how my source is : (so yes, sum(sales) to get sales for a month)

Capture.PNG

My final table will have only: customer, monthYear or (month and year) depends on how we work out the solution and the state : good or bad !

and this is what I've done so far:

t:

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

year(date)*12+month(date) as monthid;

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;

a:

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

Concatenate

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

drop table t;

load Month,Year,etat 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;

my excel source is attached !

THANK you Sunny !

sunny_talwar

Something like this

Table:

LOAD id,

  MonthYear,

  Sum(sales) as Sales

Group By id, MonthYear;

LOAD id,

    MonthName(date) as MonthYear,

    sales

FROM

[..\..\Downloads\etat.xlsx]

(ooxml, embedded labels, table is Feuil1);

FinalTable:

LOAD *,

  If(Sales > Alt(Previous(Sales), 0) and Sales > Alt(Previous(Previous(Sales)), 0), 'Good', 'Bad') as State

Resident Table

Order By id, MonthYear;

DROP Table Table;

Capture.PNG

OmarBenSalem
Author

Wanna share my earnings? haha

You're amazing !