Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

simple app table export different

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';

Tags (1)
7 Replies
Khan_Mohammed
Honored Contributor II

Re: simple app table export different

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

Not applicable

Re: simple app table export different

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.

Khan_Mohammed
Honored Contributor II

Re: simple app table export different

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

Not applicable

Re: simple app table export different

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

Khan_Mohammed
Honored Contributor II

Re: simple app table export different

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?


Not applicable

Re: simple app table export different

I loaded the script from above.  I just copied it pasted it in and loaded.

Added on filter pane for billed is a date field.

I did not include dates in the table.

Excel file total is 5,562,297.38

rows in Excel 168,280

Rows in Sheet  174,625

Khan_Mohammed
Honored Contributor II

Re: simple app table export different

How many rows you getting without selecting any filter?

Just export it as it is, with out month filter?

The rows are missing cus of null or problem with dates, month or year etc.

I'm not sure, can you share your sample data?