topic Re: Trial Balance Report - How to add sub-column in Pivot Table? in QlikView

Trial Balance Report - How to add sub-column in Pivot Table?

Sokkorn — Wed, 24 Aug 2011 11:04:56 GMT

Hi All,

Currently I try to generate Trial Balance report. And my problem is on format the report.

1. Here is the pivot table format in QlikView:

AccID	PER01	PER02	PER03
111-10-10-10	$0.00	$0.00	($2,809.00)
111-10-10-20	$0.00	($1,154.00)	$25.00
111-20-10-10	$2,800.00	$2,800.00	$1,500.00
111-30-10-10	($1,000.00)	$1,000.00	$2,300.00
112-10-10-10	$1,000.00	$1,500.00	$2,700.00
112-10-10-20	$0.00	$1,390.00	$570.00
112-11-10-10	$3,698.00	$2,000.00	$0.00

2. The format that I need is add Debit/Credit column in each column PER01,PER02,PER03

(If amount>0 then we put it in Debit column and if amount<0 then we put in Credit column)

AccID	PER01		PER02		PER03
AccID	Debit	Credit	Debit	Credit	Debit	Credit
111-10-10-10	$0.00		$0.00		$0.00	$2,809.00
111-10-10-20	$0.00		$0.00	$1,154.00	$25.00
111-20-10-10	$2,800.00		$2,800.00		$1,500.00
111-30-10-10	$0.00	$1,000.00	$1,000.00		$2,300.00
112-10-10-10	$1,000.00		$1,500.00		$2,700.00
112-10-10-20	$0.00		$1,390.00		$570.00
112-11-10-10	$3,698.00		$2,000.00		$0.00

How can I add column Debit/Credit in QlikView like format(2)?

Please help to check and modify in the attached file.

Regards,

Sokkorn Cheav

Re: Trial Balance Report - How to add sub-column in Pivot Table?

its_anandrjs — Wed, 24 Aug 2011 11:59:49 GMT

Hi,

Its look easy when ever you want to display a table like try to load data in different ways which makes easy.

Here look at the sample attached file try to load data in this manner.

Regards,

Anand

Re: Trial Balance Report - How to add sub-column in Pivot Table?

its_anandrjs — Wed, 24 Aug 2011 12:08:54 GMT

Hi,

See the latest updated sample file Qvw file it is updated.

Regards,

Anand

Re: Trial Balance Report - How to add sub-column in Pivot Table?

Sokkorn — Thu, 25 Aug 2011 00:01:47 GMT

Hi Anand,

Thanks for your reply. In my datasource have only field AccID,PER01,PER02,PER03 and not load data from Excel also. So Debit/Credit column need to customize in pivot table.

Do you have any idea besides above solution? Please take a look in my attached file and modify on it.

Appreciated your solution.

Regards,

Sokkorn

Re: Trial Balance Report - How to add sub-column in Pivot Table?

qliksus — Thu, 25 Aug 2011 06:00:12 GMT

See the attached file

Re: Trial Balance Report - How to add sub-column in Pivot Table?

Sokkorn — Thu, 25 Aug 2011 06:06:26 GMT

Hi qliksus,

Thanks for your reply. It correct.