Help create flags in script

[mrthomasshelby]

Hello,

I have a simple data table as shown below:

F11	F21	Candidate 1	Candidate 2	Candidate 3	Candidate 4	Candidate 5	Candidate 6	Candidate 7	Candidate 8
	No of CV Required	1	1	1	1	1	1	1	1
	CV Submitted by Bidder	1	1	1	1	1	0	0	0
Personal Detail	Is Employee's status in Bidder's Company currently Permanent?	YES	YES	NO	YES	YES
Education Background	Select Secondary Qualification/Education Level	L2	L1	L1	L1
Working Experiences	Total Working Experience	5	4	6	7	8
Supporting Documents	Have you submitted documents	YES	YES	YES	YES	YES
Supporting Documents	Have you submitted - Candidate’s Education Certificate	YES	NO	YES	YES	YES

I'm trying to build flags based on some specific criteria:

1. Flag1 is based on the field 'CV Submitted by Bidder'. If Total Number of CVs submitted by bidder <=5, Fail,Pass.

2. Flag 2 is based on the field 'Is Employee's status in Bidder's Company currently Permanent?'. IF no. of 'YES' responses to this question <5, Fail, Pass.

3. Flag 3 is based on the field Select Secondary Qualification/Education Level . If <5 blank responses, fail, pass.

4. Flag 4 is based on the field Total Working Experience. If 5 Candidates have less than 5 years of experience, then FAIL; less than 7 years of experience, OKAY,else Pass.

5. Flag 5 is based on the field Have you submitted documents. If < 5 responses are "YES", then Fail, else Pass.

6. Flag 6 is based on the field Have you submitted - Candidate’s Education Certificate. If < 5 responses are "YES", then Fail,else Pass.

I have run out of ideas on how I can transform the table to convenience and build all of these flags in the script itself. Please help me with the transformation and the logics for the flags as above. Any help is much much appreciated! Thanks in advance!

stalwar1‌

[sunny_talwar]

May be first transform your table using The Crosstable Load and then create flags

[mrthomasshelby]

Hi Sunny! I have tried converting my table as a crosstable as follows:

CrossTable(Criteria,Responses)

Load

F11,

F21,

[ Candidate 1],

[Candidate 2],

[Candidate 3],

[Candidate 4],

[Candidate 5],

[Candidate 6],

[Candidate 7],

[Candidate 8];

But nothing seems to happen to the table. Perhaps just help me out with just this crosstable transformation as I have never used it before? TIA!

[sunny_talwar]

Seems like you have two fields before you Candidate begins, right... may be try this

CrossTable(Criteria,Responses, 2)

Load F11,

    F21,

    [ Candidate 1],

    [Candidate 2],

    [Candidate 3],

    [Candidate 4],

    [Candidate 5],

    [Candidate 6],

    [Candidate 7],

    [Candidate 8]

FROM .....;

[mrthomasshelby]

Got what I was dong wrong. Need to indicate the number of key fields correctly. Now I got the table transformed. Hopefully, I can build all the flags easily too. Thanks a lot Sunny! Much appreciated!

[sunny_talwar]

No problem at all

[mrthomasshelby]

Hi Sunny, Sorry to be a bother but here's a doubt. I've written this expression for creating my flag 1(If no.of CVs submitted by Bidder<=5,Fail) in the script after transforming the table into a cross table:

if(F21='CV Submitted by Bidder' and Sum(Responses)<=5, 'Fail','Pass') as Flag15

However, I get an error saying Invalid expression. What am I doing wrong and what's the correct way to create this flag?

[sunny_talwar]

You are using an aggregation in the script... did you use a group by statement?

[mrthomasshelby]

I have tried writing it this way:

if(F21='CV Submitted by Bidder' and Sum(Responses)<=5,'Fail','Pass') as Flag15

Resident T2_1

Group by F21;

Drop Table T2_1;

[sunny_talwar]

How about this

If(Sum(If(F21 = 'CV Submitted by Bidder', Responses)) <= 5, 'Fail', 'Pass') as Flag15

Resident T2_1;

Drop Table T2_1;