Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Weekly trend data delivered in Columns

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

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand

View solution in original post

9 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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

reddy-s
Master II
Master II

Hi Josh,

Gysbert's reply will help convert the data into a plotable format. You can also check this out :  Loading Cross Tables

Anonymous
Not applicable
Author

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

reddy-s
Master II
Master II

Hi Josh,

Check this out:

Capture.PNG

I am even attaching the sample file:

Thanks,

Sangram.

reddy-s
Master II
Master II

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.

Anonymous
Not applicable
Author

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

reddy-s
Master II
Master II

Hi Josh,

That's doable!

I will post an other qvf with your requirement.

reddy-s
Master II
Master II

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:

Capture.PNG

Thanks,

Sangram.