Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Hi Lily,
On what basis, you are calculating the C column.? Do you have any formula/ Criteria? Could you elaborate..!!!
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')
Hi Lilly,
Could you please send any sample qvw.
Thank you,
Satya Paleti
what is logic to calculate Column C?
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
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
Check the attachment and let me know.
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 ];
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