Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
mrthomasshelby
Contributor 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

Re: Help create flags in script

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;

18 Replies

Re: Help create flags in script

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

mrthomasshelby
Contributor III

Re: Help create flags in script

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!

Re: Help create flags in script

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

Re: Help create flags in script

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!

Re: Help create flags in script

No problem at all

mrthomasshelby
Contributor III

Re: Help create flags in script

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?

Re: Help create flags in script

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

mrthomasshelby
Contributor III

Re: Help create flags in script

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;

Re: Help create flags in script

How about this

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

Resident T2_1;

Drop Table T2_1;