How to add a calculated column in the script?

[#1]

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

[tamilarasu]

Hi Lily,

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

[ramasaisaksoft]

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

[SatyaPaleti]

Hi Lilly,

Could you please send any sample qvw.

Thank you,

Satya Paleti

[Kushal_Chawda]

what is logic to calculate Column C?

[sasiparupudi1]

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

[#7]

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

[tamilarasu]

Check the attachment and let me know.

[Kushal_Chawda]

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 ];

[Mark_Little]

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