7 Replies Latest reply: Mar 31, 2017 10:50 AM by Aehman K RSS

    simple app table export different

    Robert Winkel

      I am having trouble with a APP.

      If I add a KPI and filter it down to one month the total is higher than what it is when I export report and add the column up and it does not match?

      In excel 169,748 rows and it should have 174,521 rows.

      on the latest version of sense.

       

      LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

      LOAD Billed,
          FacID,
          RxNo,
           DispenseDt,
          NDC,
          Qty,
          MOP,
          BillAmt,
          InvoiceGrp,
          DrugLabelName,
          IsACopay,
          OtherMOP;
      SQL SELECT Billed,
          FacID,
          RxNo,
           DispenseDt,
          NDC,
          Qty,
          MOP,
          BillAmt,
          InvoiceGrp,
          DrugLabelName,
          IsACopay,
          OtherMOP
      FROM FwReports.dbo.Billing

      Where Billed >= '2017-01-01 00:00:00';

        • Re: simple app table export different
          Aehman K

          The problem might be with your Date fields, check in XL file for which value you getting less or more records

            • Re: simple app table export different
              Robert Winkel

              I just took a crystal report ran it.  used the same data imported to Excel then took that excel file loaded it into Qlik created a table not filtering anything.  exported and the totals are different.

               

              I also removed all data fields and it still will not export correct amount.

               

              The KPI that totals billed amount is the correct amount.

                • Re: simple app table export different
                  Aehman K

                  FROM FwReports.dbo.Billing

                  Where Billed >= '2017-01-01 00:00:00';



                  I don't know what your Where function is doing. It's not the DATA, your DATE FIELD might have issues when getting transformed.

                  Try with full raw data without Where clause, export it, Import the XL, run it in Qlik and then export it again?

                  If you get same no of records then indeed it was issue with your WHERE clause

                    • Re: simple app table export different
                      Robert Winkel

                      I tried that, removed all filters and where clauses.  an pulled in minimal date no luck.

                       

                      How can it drop 4,000 rows?

                       

                      When I loaded from an excel file that had 174000 rows then when I export it also drops rowsto 169000

                        • Re: simple app table export different
                          Aehman K

                          LIB CONNECT TO 'pharmoresql production (pharmore_rwinkel)';

                          LOAD Billed,
                              FacID,
                              RxNo,
                               DispenseDt,
                              NDC,
                              Qty,
                              MOP,
                              BillAmt,
                              InvoiceGrp,
                              DrugLabelName,
                              IsACopay,
                              OtherMOP;
                          SQL SELECT Billed,
                              FacID,
                              RxNo,
                               DispenseDt,
                              NDC,
                              Qty,
                              MOP,
                              BillAmt,
                              InvoiceGrp,
                              DrugLabelName,
                              IsACopay,
                              OtherMOP
                          FROM FwReports.dbo.Billing



                          Just the RAW DATA from above script should give you matching records even after exporting to XL, Importing same XL back. Usually it's the dates which gets miss-matched and records go missing or extra records are added.

                          If you extracted the data without any transformation then it should same in both XL's and if it doesn't then I'm not sure why it is happening?