Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to add a calculated column in the script?

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 VarianceTimesheet Report StatusCompliance category
1PostedNonCompliant 1
1Approved NonCompliant 1
FALSEPostedCompliant
FALSEApproved Compliant
FALSEOpen/SubmittedNonCompliant 2
1Open/SubmittedNonCompliant 2
9 Replies
tamilarasu
Champion
Champion

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
Creator III
Creator III

Hi Lilly,

Could you please send any sample qvw.

Thank you,

Satya Paleti

Kushal_Chawda

what is logic to calculate Column C?

sasiparupudi1
Master III
Master III

Untitled.png

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

Not applicable
Author

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
Champion
Champion

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
Luminary
Luminary

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