Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

joshmartyne
New Contributor II

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

Re: Weekly trend data delivered in Columns

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
9 Replies

Re: Weekly trend data delivered in Columns

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
joshmartyne
New Contributor II

Re: Weekly trend data delivered in Columns

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

reddys310
Honored Contributor II

Re: Weekly trend data delivered in Columns

Hi Josh,

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

joshmartyne
New Contributor II

Re: Weekly trend data delivered in Columns

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

reddys310
Honored Contributor II

Re: Weekly trend data delivered in Columns

Hi Josh,

Check this out:

Capture.PNG

I am even attaching the sample file:

Thanks,

Sangram.

reddys310
Honored Contributor II

Re: Weekly trend data delivered in Columns

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.

joshmartyne
New Contributor II

Re: Weekly trend data delivered in Columns

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

reddys310
Honored Contributor II

Re: Weekly trend data delivered in Columns

Hi Josh,

That's doable!

I will post an other qvf with your requirement.

reddys310
Honored Contributor II

Re: Weekly trend data delivered in Columns

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.

Community Browser