Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I receive that displays a trend over time. The data is delivered in separate columns for each week.
How would I manipulate the data within Qlik Sense so that I could use a filter box to filter by week. Currently the filterbox will only let me Select the column title (Eg Week 20) and then the filter box will populate with the column contents.
Please see attached excel for an snippet of the data received.
Best,
Josh
Use a CrossTable load to load your data.
CrossTable(Week,Value)
LOAD
RecNo() as Record,
*
FROM
[c7.xlsx]
(ooxml, embedded labels, table is Sheet1)
;
Result:
LOAD
Record,
Num#(SubField(Week,'_',2)) as Week,
Num#(SubField(Week,'_',3)) as Year,
SubField(Week,'_',4) as Type,
Value
RESIDENT
Temp
;
DROP TABLE Temp;
You can then use the Week field in a listbox to filter the data.
Use a CrossTable load to load your data.
CrossTable(Week,Value)
LOAD
RecNo() as Record,
*
FROM
[c7.xlsx]
(ooxml, embedded labels, table is Sheet1)
;
Result:
LOAD
Record,
Num#(SubField(Week,'_',2)) as Week,
Num#(SubField(Week,'_',3)) as Year,
SubField(Week,'_',4) as Type,
Value
RESIDENT
Temp
;
DROP TABLE Temp;
You can then use the Week field in a listbox to filter the data.
Thanks for the Help Gysbert.
Im new to qlik sense and have yet to get my head fully around the data load editor.
I've done some research on crosstables but haven't been able to crack it.
I wonder if you could help me further? I've attached more data and my QVF
My goal is to be able to view the data in a number of ways. For instance 2 bar charts.
1. See load time by market and then use a filter pane to filter by week
2. See load time by week (For instance for a single market) and trend over time
Hope you can help.
Josh
Hi Josh,
Gysbert's reply will help convert the data into a plotable format. You can also check this out : Loading Cross Tables
Hi Sangram,
This video was really helpful. However its for QlikView.
Is there a similar helper within qliksense that will help me create cross tables?
Josh
Hi Josh,
Check this out:
I am even attaching the sample file:
Thanks,
Sangram.
HI Josh,
The code works the same in qliksense as well.
I have solved it for you. I have even attached the file for you in my previous comment.
Thanks,
Sangram.
Hi Sangram,
Thanks very much for your Answer.
It worked well and I managed to create the crosstable.
However I have one more request. How can I split my cross table up into the two elements?
- Load time
- Requests
and have them linked by the filter 'week' so that changing a week could change both data points lets say in a stacked bar chart.
What happens is that when I create a filter is that I can either have both connected together, or I can create multiple cross tables but then I have to have two independent filters for 'Load week' and 'request week'
Thank for your help so far.
Best,
Josh
Hi Josh,
That's doable!
I will post an other qvf with your requirement.
Hi Josh,
Here is the script :
[Cross Table Load]:
CrossTable(Load_Time, Data, 4)
LOAD * FROM
[lib://Desktop/Load time by week.xlsx]
(ooxml, embedded labels, table is [CZ Example 3 Levels]);
[Transformed LT]:
load * where not isnull([LT data]);
load Journey as [LT Journey],
Timer as [LT Timer],
Percentile as [LT Percent],
Market as [LT Market],
right(Load_Time,8) as [textLT],
if(right(Load_Time,8) = 'loadTime',Data) as [LT data],
replace(mid(Load_Time,6,7),'_','-') as [Week Year]
Resident [Cross Table Load];
noConcatenate
[Transformed RQ]:
load * where not isnull([RQ data]);
load Journey as [RQ Journey],
Timer as [RQ Timer],
Percentile as [RQ Percent],
Market as [RQ Market],
right(Load_Time,8) as [textRQ],
if(right(Load_Time,8) = 'requests',Data) as [RQ data],
replace(mid(Load_Time,6,7),'_','-') as [Week Year]
Resident [Cross Table Load];
drop table [Cross Table Load];
This is the data model formed after the script has been executed:
Thanks,
Sangram.