Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
My first post. I'm very new to Qliksense and have found a script (see below) that suits my purposes very well. However I am having difficulty understanding how to use my own data in it, instead of the auto-generated data the script provides as an example. Can anyone help?
For context, I got the script from this thread, https://community.qlikview.com/message/256243#256243
Thanks.
---
/* This section just creates some test data */
data:
LET vEndDate = num(today(1)); // End at today's date
LET vStartDate = num(addmonths(today(1),-14)) -1; // Start at 14 months earlier than today
TestData:
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;
/* End of test data */
/* This script demonstrates assigning Dates to named groups, such as "Yesterday" or "Last Week".
Date table was created on the previous tab.
*/
// Assign current date to a variable. Makes INLINE below a little neater and ensures we don't cross midnight boundry.
LET vToday=today(1);
Ranges:
// Use the "evaluate()" function to execute the input function text and convert it to actual date values.
LOAD Range, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd
;
/* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range.
The Start/End values are defined using standard QV date functions.
Semicolon is used for a field delimeter because some of the functions contain commas.
*/
LOAD * INLINE [
Range; RangeStart; RangeEnd
Today; vToday; DayEnd(vToday)
Yesterday; vToday-1; DayEnd(vToday-1)
This Week; WeekStart(vToday); WeekEnd(vToday)
Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1)
Last 2 Weeks; WeekStart(today(1),-1); WeekEnd(today(1))
This Month; MonthStart(vToday); MonthEnd(vToday)
Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1)
Last Two Months; MonthStart(vToday,-1); MonthEnd(vToday)
This Year; YearStart(vToday); YearEnd(vToday)
Last Year; YearStart(vToday,-1); YearEnd(vToday,-1)
] (delimiter is ';')
;
/*
Use IntervalMatch to link the Date field into multiple Range fields.
Doing a JOIN avoids creating a messy synthetic key.
*/
JOIN (Ranges) IntervalMatch (Date) LOAD RangeStart, RangeEnd RESIDENT Ranges;
// Because we did a JOIN, we may drop the the Start/End fields.
DROP FIELDS RangeStart, RangeEnd;
Please share your data or app.
Thank you!
What are you trying to do?
The above script will run and creates Inline table with two fields (Date and Range)
If you create a new app and then copy this script into the Script Editor and then run that is what you will end up with.
You can then just create a simple table on a sheet and load the two fields to see what you got.
Never seen that script before, it looks quite good.
It creates 2 tables called Ranges and TestData, which are joined via the common field Date.
Assuming your data has a date field then load your data renaming the date field to Date and it should auto join in. Then you can create a Filter Pane against the Range field, create a table with your data in it and select a Range value and see what happens.
For tidiness you could drop the table called TestData.