7 Replies Latest reply: Aug 4, 2016 4:01 PM by Padmanabha Kamath RSS

    QlikSense - How can you render a qvd in a report ?

    Padmanabha Kamath

      I have generated a QVD using a query (QVS). This query does a "Group By" and then "OrderBy" on rows of a database table. It is generated successfully and when I select the qvd in the data load editor, I can see the sorted rows.

       

      I have a few questions on this :

      - I am a little confused by this as I read multiple posts which said that sorting can be done only on Resident tables. I am not using resident tables at all while generating this qvd and I can see the sorted rows in the data load editor.

      - How can I view this qvd data in a report ? IN other words, how do I add this qvd as a sheet object ? That way, I can use the configurator to get a URL to the report.

      - How do I periodically refresh this qvd with the latest data ? I would like the query to be executed 4 times a day and the qvd to be refreshed so that anybody consuming the report would be see the latest data.

       

      Thank you !

        • Re: QlikSense - How can you render a qvd in a report ?
          Stefan Wühl

          W.r.t the order of records: I assume you are using ORDER BY in the SQL / SELECT part of your script statement.

           

          The posts mentioning that ordering is only possible with resident tables are referring to a LOAD statement with an order by clause.

           

           

          If you want to show the all data in your app, you can load the table using the data load editor (maybe a  field with a unique key e.g. using Recno() ), then create a straight table chart in your UI and drag in all fields as dimensions.

            • Re: QlikSense - How can you render a qvd in a report ?
              Padmanabha Kamath

              The posts mentioning that ordering is only possible with resident tables are referring to a LOAD statement with an order by clause.

              Got it. This means that Resident tables are to be used when further transforming data from QVDs.

               

              This is how my QVD looks like (sensitive data blacked out).  I would like to add this to a sheet as an object so that I can get a link to this in the Single Configurator . QVD_View.PNG

               

              But I cannot get the entire table into the sheet. I can see the two columns from the above QVD but when I add them individually, the data is all out of sync and does not resemble the data as seen in the qvd above.

              Sheet_View.PNG

                • Re: QlikSense - How can you render a qvd in a report ?
                  Greg Williams

                  Qlik provides the ability to manipulate data. The Sort By clause gives the ability to sort data. You are then able to store the results into a QVD. In the front end, if desired, you can control the sort order of the rows by not selecting sort, which should default to the natural load order of the data. You could consider using the autonumber feature to count records and sort off of that. You could introduce creating/using an inline table that provides the ability to map records to your desired sort order too. If I am understanding your use case, there are numerous methods to address. Sort By Asc seems to make the most sense. You can store this table of data into a QVD. Remember, QVDs can be tiered (i.e. layered)...you can have numerous levels (layers) of QVDs.

                  -gw

                    • Re: QlikSense - How can you render a qvd in a report ?
                      Padmanabha Kamath

                      Agreed, but what I am trying to say is that my QVD already has the sorted data which I can view (see a representative of this in the first pic one post back). As an illustration, I can see something like the table below in my QVD. I can see this QVD data via the Data Load Editor.

                      TABLE 1

                      User ID           TotalSales

                      323                    50

                      2                        40

                      23                      30

                      567                    20

                      29                      10

                       

                      Now I reference this data in my app ("Add Data" via Data manager) and then add Tables to the sheet. Now, (reference the second pic in my previous post) when I try to add the columns one by one from the drop down as "Dimensions", I am expecting Table 1 above. Instead, I see the table below. As you can see, Qlik has sorted each row ...... and this is total garbage compared to Table 1 !

                      User ID           TotalSales

                      2                           10

                      23                          20

                      29                          30

                      323                        40

                      567                        50

                       

                      This is just my third day using Qlik, so apologies if I am missing something basic !

                    • Re: QlikSense - How can you render a qvd in a report ?
                      Stefan Wühl

                      I can't really see much in your screen shots, specifically how you have created your UI object.

                       

                      My suggestion was to use a straight table chart object and use all fields as dimensions and no expression.

                       

                      You would need a primary key in your data table to see all records (since dimensions only show unique combinations).