Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
narender123
Specialist
Specialist

Cross table problem

Hi All,

I am using a cross table.

Like:-

TableA:

    DECLARATION_ID,

   CUSTOMS_VALUE

     ITEM_NO,

    EXPORT_DUTY_EXEMPTED,

     EXCISE_DUTY_EXEMPTED

FROM TABLEA;

Expression in report:

=Sum(CUSTOMS_VALUE) is showing right.

but when use cross table

CrossTable(TAX_CODE,TAX,3)

NEW:

LOAD DECLARATION_ID,

        CUSTOMS_VALUE

       ITEM_NO,

       EXPORT_DUTY_EXEMPTED,

       EXCISE_DUTY_EXEMPTED

RESIDENT TableA;

DROP TABLE TableA;


Expression in report:

=Sum(CUSTOMS_VALUE) is showing wrong.

Why after cross table CUSTOMS_VALUE is showing wrong??

Thanks,

Narender

1 Solution

Accepted Solutions
sunny_talwar

Because after the CrossTable() Load  the field CUSTOMS_VALUE will repeat and you will get a much higher number. Try this after CrossTable() Load:

Sum(Aggr(Only(CUSTOMS_VALUE), DECLARATION_ID))

View solution in original post

10 Replies
Anonymous
Not applicable

Could you share your excel table or sample qvw?

Tax Code exist into your table?

Why you are applying Cross Table on same table after taking resident of same table?

check this:

The Crosstable Load

What is cross table? Where it can be used?

sunny_talwar

Because after the CrossTable() Load  the field CUSTOMS_VALUE will repeat and you will get a much higher number. Try this after CrossTable() Load:

Sum(Aggr(Only(CUSTOMS_VALUE), DECLARATION_ID))

Not applicable

you can use aggr function

narender123
Specialist
Specialist
Author

Thanks Sunny.Its working.

Could you tell me , use of "Only" in aggr?

sunny_talwar

So since the same number for CUSTOMS_VALUE is repeating for a single DECLARATION_ID, I just used the only function to get its value. You can use Avg(CUSTOMS_VALUE) or Sum(DISTINCT CUSTOMS_VALUE) and they will give you the exact same output.

Sum(Aggr(Only(CUSTOMS_VALUE), DECLARATION_ID))

Sum(Aggr(Avg(CUSTOMS_VALUE), DECLARATION_ID))

Sum(Aggr(Sum(DISTINCT CUSTOMS_VALUE), DECLARATION_ID))

narender123
Specialist
Specialist
Author

Got it.

Thanks,

narender123
Specialist
Specialist
Author

Thanks Balraj.

sunny_talwar

Awesome

If your issue is resolved, can you close the thread by marking correct answer.

Best,

Sunny

Anonymous
Not applicable

I have not done anything, say thanks to Sunny