topic Re: How to add a calculated column in the script? in QlikView

How to add a calculated column in the script?

Tue, 03 Nov 2015 02:59:58 GMT

I want to add a calculated column in the script to achieve a Compliance category. I am not quite sure how to do it, can anyone help?

Below table, column A & B is existing data, and Column C is what I want to add in the script.

A B C

Compliance Variance	Timesheet Report Status	Compliance category
1	Posted	NonCompliant 1
1	Approved	NonCompliant 1
FALSE	Posted	Compliant
FALSE	Approved	Compliant
FALSE	Open/Submitted	NonCompliant 2
1	Open/Submitted	NonCompliant 2

Re: How to add a calculated column in the script?

tamilarasu — Tue, 03 Nov 2015 04:53:23 GMT

Hi Lily,

On what basis, you are calculating the C column.? Do you have any formula/ Criteria? Could you elaborate..!!!

Re: How to add a calculated column in the script?

ramasaisaksoft — Tue, 03 Nov 2015 05:44:18 GMT

Hi Lily,

you can add column by doing Resident load

else

if there is only 2 conditions then u can use 3 rd column as if(a*b>10,'Complaint','Non-Complaint')

Re: How to add a calculated column in the script?

SatyaPaleti — Tue, 03 Nov 2015 05:45:44 GMT

Hi Lilly,

Could you please send any sample qvw.

Thank you,

Satya Paleti

Re: How to add a calculated column in the script?

Kushal_Chawda — Tue, 03 Nov 2015 06:00:44 GMT

what is logic to calculate Column C?

Re: How to add a calculated column in the script?

sasiparupudi1 — Tue, 03 Nov 2015 06:36:41 GMT

Try ApplyMap

map:

Mapping Load * Inline

[

From ,To

1Posted,NonCompliant 1

1Approved,NonCompliant 1

FALSEPosted,Compliant

FALSEApproved,Compliant

FALSEOpen/Submitted,NonCompliant 2

1Open/Submitted,NonCompliant 2

];

Load [Compliance Variance],[Timesheet Report Status]

,ApplyMap('map',[Compliance Variance]&[Timesheet Report Status],'NA') as [Compliance category]

;

Load * Inline

[

Compliance Variance,Timesheet Report Status

1, Posted

1 ,Approved

FALSE, Posted

FALSE ,Approved

FALSE, Open/Submitted

1 ,Open/Submitted

];

hth

Sasi

Re: How to add a calculated column in the script?

Tue, 03 Nov 2015 22:38:14 GMT

Logic behind column C is:

>>IF column Timesheet Report Status="Approved" OR "Posted" AND column Compliance Variance ="1" THEN "NonCompliant 1"

>> IF column Timesheet Report Status="Approved" OR "Posted" AND column Compliance Variance = "FALSE" THEN "Compliant"

>>IF column Timesheet Report Status= "Open/Submitted" AND column Compliance Variance="1" or "FALSE" THEN "NonCompliant 2"

I want to create this column C in the script so I can show the different compliance category in the bar chart at the front end.

Cheers,

Lily

Re: How to add a calculated column in the script?

tamilarasu — Wed, 04 Nov 2015 09:49:57 GMT

Check the attachment and let me know.

Re: How to add a calculated column in the script?

Kushal_Chawda — Wed, 04 Nov 2015 10:02:42 GMT

try this

Data:

LOAD *, if(match([Timesheet Report Status],'Posted','Approved') and [Compliance Variance]='1','NonCompliant 1',

if(match([Timesheet Report Status],'Posted','Approved') and [Compliance Variance]='FALSE','Compliant',

if([Timesheet Report Status]='Open/Submitted' and match([Compliance Variance],'FALSE','1'),'NonCompliant 2'))) as [Compliance Category]

Inline [

Compliance Variance, Timesheet Report Status

1, Posted

1, Approved

FALSE, Posted

FALSE, Approved

FALSE, Open/Submitted

1, Open/Submitted ];

Re: How to add a calculated column in the script?

Mark_Little — Wed, 04 Nov 2015 10:15:02 GMT

Hi,

You can add this to your load script

IF([Timesheet Report Status]= 'Approved' OR [Timesheet Report Status]= 'Posted',

IF([Compliance Variance]=1,

'NonCompliant 1',

	IF([Compliance Variance] = 'FALSE',
	'Compliant'

)

IF([Timesheet Report Status]= 'Open/Submitted' AND ([Compliance Variance]='1' OR [Compliance Variance]='FALSE'),

'NonCompliant 2'

)

) AS [Compliance category]

Hope this helps

Mark