topic Re: Complex crosstable conversion in QlikView

Complex crosstable conversion

Thu, 27 Mar 2014 12:10:46 GMT

Hi all,

I needed some help in converting a payslip into a linear table. Currently this is the table I have in Excel:

160897	J Bloggs		Dep 1233 R979
Net	1199.68		Run date 23/11/12 (21/10/12 - 17/11/12)
Basic	160.0000		@ 8.8400	1414.4
Basic@1.5	2.0000		@ 13.2600	26.52
TOTAL GROSS PAY		1477.36
Gross YTD	13190.86		Prev emp pay	0
Taxable YTD	13190.86		Prev emp tax	0
Tax YTD	1515.2
WPBP - LA pay	13190.86
160898	A Cloggs		Dep 1233 R979
Net	2323.23		Run date 23/11/12 (21/10/12 - 17/11/12)
Basic	160.0000		@ 8.8400	1213.4
Basic@1.5	2.0000		@ 13.2600	26.52
TOTAL GROSS PAY		1477.36
Gross YTD	13190.86		Prev emp pay	0
Taxable YTD	13190.86		Prev emp tax	0
Tax YTD	1515.2
WPBP - LA pay	13190.86
160899	M Bobbs		Dep 1233 R979
Net	4535.76		Run date 23/11/12 (21/10/12 - 17/11/12)
Basic	160.0000		@ 8.8400	1414.4
Basic@1.5	2.0000		@ 13.2600	5345.8
TOTAL GROSS PAY		1477.36
Gross YTD	13190.86		Prev emp pay	0
Taxable YTD	13190.86		Prev emp tax	0
Tax YTD	1515.2
WPBP - LA pay	13190.86

I want to convert it into something like this:

Net	Basic	@ 8.8400	Basic@1.5	@ 13.2600	Gross YTD	Tax YTD	WPBP - LA pay	Prev emp pay	Prev emp tax	TOTAL GROSS PAY
160897	J Bloggs	1199.68	160.0000	1414.4	2.0000	26.52	13190.86	1515.2	13190.86	0	0	1477.36
160898	A Cloggs	2323.23	160.0000	1213.4	2.0000	26.52	13190.86	1515.2	13190.86	0	0	1477.36
160899	M Bobbs	4535.76	160.0000	5345.8	2.0000	26.52	13190.86	1515.2	13190.86	0	0	1477.36

Message was edited by: sabah dada

Re: Complex crosstable conversion

arsal_90 — Thu, 27 Mar 2014 12:22:37 GMT

can u provide me the excel file

Re: Re: Complex crosstable conversion

Thu, 27 Mar 2014 12:27:26 GMT

Please find attached the excel...

Re: Re: Re: Complex crosstable conversion

arsal_90 — Thu, 27 Mar 2014 12:39:35 GMT

Please find the attached file and let me know that's u want

Re: Re: Re: Re: Complex crosstable conversion

Thu, 27 Mar 2014 12:51:01 GMT

Muhammad,

Thank you for this. However, I have a few more questions to ask following this....

1) For the category LIEU, J.Bloggs has doesn't have any values, only A.Bloggs has a LIEU value of 3.00. If I select J Bloggs, I should not get any values. Rather it is duplicating the value as seen in the example below.

2) I cannot see any of my Total Gross Pay values e.g. Gross YTD, Taxable YTD,

How can these be solved?

Many thanks for your help in advance.

Re: Re: Re: Complex crosstable conversion

arsal_90 — Thu, 27 Mar 2014 13:05:07 GMT

Hi sabah

Basically your payslip format is too complicated please formatize your pay slip format because in this format it can't be resolved by cross table. But i will try else if you can formatize a standard pattern for this then it will be good

Re: Re: Re: Re: Complex crosstable conversion

Thu, 27 Mar 2014 13:10:04 GMT

Hi Muhammad,

I agree with you. But unfortunately I can't change the format of the payslip. There are many thousands of payslips that come in each month, so i will need to work with this format.

Ideally I wanted to format the payslip in a different manner, which would make it simpler.

Re: Complex crosstable conversion

Thu, 27 Mar 2014 13:12:17 GMT

Have you tried editing the payslips in the Load Wizard (Enable Transformation Step)?

Cheers,

Lucas

Re: Re: Re: Re: Complex crosstable conversion

arsal_90 — Thu, 27 Mar 2014 13:14:59 GMT

ok tell me what the output result u want from that. please let me according to that excel file that u provided and also let me what fields is neccessary or what is not

Re: Re: Re: Re: Complex crosstable conversion

Thu, 27 Mar 2014 13:15:25 GMT

Lucas,

I have yes. I have tried to split the payslip into two. Employee Details and Data. But not obtaining the correct results..

Employee_Details:

LOAD @1 as EmployeeID,

@2 as EmployeeName

FROM

[Copy of Payroll02ManreportsPer09.xls]

(biff, no labels, table is [ctt$], filters(

Remove(Row, Pos(Top, 2)),

Remove(Row, Pos(Top, 3)),

Remove(Row, Pos(Top, 4)),

Remove(Row, Pos(Top, 4))

));

Data:

LOAD

@1 as Fields,

@2 as Data

FROM

[Copy of Payroll02ManreportsPer09.xls]

(biff, no labels, table is [ctt$], filters(

Remove(Row, Pos(Top, 1)),

Remove(Row, Pos(Top, 12)),

Remove(Row, Pos(Top, 22))

));

Re: Re: Re: Re: Re: Complex crosstable conversion

Thu, 27 Mar 2014 13:34:00 GMT

Muhammad,

Please see attached the format that I want

Re: Complex crosstable conversion

arsal_90 — Thu, 27 Mar 2014 14:36:46 GMT

Hi sabah

I try my best to do for your solution. Please see in attached file is this your desired values

Re: Complex crosstable conversion

arsal_90 — Thu, 27 Mar 2014 14:40:22 GMT

Tell me is this slip data only available in excel sheet? is there no database maintain for it. if there is database tables maintain for it then do work on that

Re: Re: Re: Re: Re: Complex crosstable conversion

Thu, 27 Mar 2014 15:27:45 GMT

Hi Muhammad,

Many thanks. This is what I am looking for. Could you kindly put a copy of the Excel file that you used for this so I can see the set up?

Thank you for your help.

Re: Complex crosstable conversion

arsal_90 — Thu, 27 Mar 2014 16:27:29 GMT

Hi Sabah

I used the excel that you provided all i do some thing in the Transformation. So sabah can u please refer this post as Answer for other people help. And if u have any other query then let me know.

Thanks & regards

Muhammad