9 Replies Latest reply: May 26, 2016 5:46 AM by Sangram Reddy RSS

    Weekly trend data delivered in Columns

    Josh Martyne

      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

        • Re: Weekly trend data delivered in Columns
          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.

          • Re: Weekly trend data delivered in Columns
            Sangram Reddy

            Hi Josh,

             

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

              • Re: Weekly trend data delivered in Columns
                Josh Martyne

                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

                  • Re: Weekly trend data delivered in Columns
                    Sangram Reddy

                    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.

                      • Re: Weekly trend data delivered in Columns
                        Josh Martyne

                        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

                          • Re: Weekly trend data delivered in Columns
                            Sangram Reddy

                            Hi Josh,

                             

                            That's doable!

                             

                            I will post an other qvf with your requirement.

                              • Re: Weekly trend data delivered in Columns
                                Sangram Reddy

                                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.