topic Re: Cross table problem in QlikView

Cross table problem

narender123 — Thu, 07 Jan 2016 10:51:13 GMT

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

Re: Cross table problem

Anonymous — Thu, 07 Jan 2016 10:55:59 GMT

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?

Re: Cross table problem

sunny_talwar — Thu, 07 Jan 2016 11:00:11 GMT

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))

Re: Cross table problem

Thu, 07 Jan 2016 12:02:56 GMT

you can use aggr function

Re: Cross table problem

narender123 — Thu, 07 Jan 2016 12:25:47 GMT

Thanks Sunny.Its working.

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

Re: Cross table problem

sunny_talwar — Thu, 07 Jan 2016 12:29:17 GMT

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))

Re: Cross table problem

narender123 — Thu, 07 Jan 2016 12:46:47 GMT

Got it.

Thanks,

Re: Cross table problem

narender123 — Thu, 07 Jan 2016 12:47:51 GMT

Thanks Balraj.

Re: Cross table problem

sunny_talwar — Thu, 07 Jan 2016 12:48:33 GMT

Awesome

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

Best,

Sunny

Re: Cross table problem

Anonymous — Thu, 07 Jan 2016 12:50:19 GMT

I have not done anything, say thanks to Sunny

Re: Cross table problem

narender123 — Thu, 07 Jan 2016 12:53:11 GMT

Thanks for your reply.