Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrthomasshelby
Creator III
Creator III

Help create flags in script

Hello,

I have a simple data table as shown below:

F11F21Candidate 1Candidate 2Candidate 3Candidate 4Candidate 5Candidate 6Candidate 7Candidate 8
No of CV Required
11111111
CV Submitted by Bidder
11111000
Personal Detail
Is Employee's status in Bidder's Company currently Permanent?
YESYESNOYESYES
Education Background
Select Secondary Qualification/Education Level
L2L1L1L1
Working Experiences
Total Working Experience
54678
Supporting Documents
Have you submitted documents
YESYESYESYESYES
Supporting Documents
Have you submitted - Candidate’s Education Certificate
YESNOYESYESYES

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

1 Solution

Accepted Solutions
sunny_talwar

May be this?

T2:

LOAD A as F11,

    B as F21,

    C as [Candidate 1],

    D as [Candidate 2],

    E as [Candidate 3],

    F as [Candidate 4],

    G as [Candidate 5],

    H as [Candidate 6],

    I as [Candidate 7],

    J as [Candidate 8]

FROM

[*Book1.xlsx]

(ooxml, no labels, header is 3 lines, table is [APPENDIX V (G)]);

T2_1:

CrossTable(Criteria, Responses,2)

Load

F11 as F12,

F21 as F22,

[ Candidate 1],

    [Candidate 2],

    [Candidate 3],

    [Candidate 4],

    [Candidate 5],

    [Candidate 6],

    [Candidate 7],

    [Candidate 8]

Resident T2;

DROP Table T2;

Left Join (T2_1)

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

Resident T2_1;

View solution in original post

18 Replies
sunny_talwar

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

mrthomasshelby
Creator III
Creator III
Author

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

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

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

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;