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: 
Not applicable

Do you create a subset of data using preceding or resident load?

Hi,

I'm still very new to this so maybe I'm thinking about this in the wrong way, or maybe I'm just not understanding the syntax or logic needing to be applied to Preceding and/or Resident loading.

My issues is that I have about ~300 rows loaded from an excel sheet that are essentially:

Name  |    Status_Now    |    Status_LastMonth    |    Status_Last2Month
"ABC"            "Green"                  "Amber"                            "Red"

"DEF"            "Green"                  "Green"                            "Green"

What I'd like to do is manipulate this data so I can show a stacked bar chart of the total number of Green/Amber/Reds over the last 3 months.

Do you do that manipulation via a resident load after the initial load of the excel data?

EDIT: I guess the more pressing concern is how do I make any visualisation dynamically recognise that Status_Now/Status_LastMonth/Status_Last2Month mean Month(Today())/Month(Today())-1/Month(Today())-2, respectively?

1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Matthew,

I always assume people have View (my mistake).

Here's the load

SampleData:

LOAD Name,

     Status_Now as Status,

     Today() as Date   

FROM

(ooxml, embedded labels, table is Plan1);

LOAD Name,

     Status_LastMonth as Status,

     AddMonths(Today(),-1) as Date   

FROM

(ooxml, embedded labels, table is Plan1);

LOAD Name,

     Status_Last2Month as Status,

     AddMonths(Today(),-2) as Date   

FROM

(ooxml, embedded labels, table is Plan1);

And for expressions, go with (each is an individual measure):

count({<Status={'Red'}>}Status) // for red count

count({<Status={'Green'}>}Status) // for green coutn

count({<Status={'Amber'}>}Status) // for amber

That gives the following table

Sample.png

View solution in original post

4 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Matthew,

See the attached QVW for an example on how to do it.

About the Resident or precedent load, is up to you really on how to do it because both are similar solutions, in my solution I just loaded the table 3 times with different fields (similar to a Concatenate).

Hope it helps.

Felipe.

Not applicable
Author

Hi Felip,

Thanks for the response. Unfortunately our QlikSense application is locked down at work and I don't think I can load this QVW application (I only have access to a share where I put data files to import, like excel files). Unless of course I don't know how to do but it is possible.

Would it be possible to post just how you load the table? Sorry for the inconvenience.

Matthew

felipedl
Partner - Specialist III
Partner - Specialist III

Hi Matthew,

I always assume people have View (my mistake).

Here's the load

SampleData:

LOAD Name,

     Status_Now as Status,

     Today() as Date   

FROM

(ooxml, embedded labels, table is Plan1);

LOAD Name,

     Status_LastMonth as Status,

     AddMonths(Today(),-1) as Date   

FROM

(ooxml, embedded labels, table is Plan1);

LOAD Name,

     Status_Last2Month as Status,

     AddMonths(Today(),-2) as Date   

FROM

(ooxml, embedded labels, table is Plan1);

And for expressions, go with (each is an individual measure):

count({<Status={'Red'}>}Status) // for red count

count({<Status={'Green'}>}Status) // for green coutn

count({<Status={'Amber'}>}Status) // for amber

That gives the following table

Sample.png

Not applicable
Author

Hi Felip,

This worked perfectly! I apologise for not saying something sooner. I thought I had responded and I didn't This has worked wonders for me and also given me a good idea of how to structure the data in the load editor. Thank you!

Matthew