topic Re: Help create flags in script in QlikView

Help create flags in script

mrthomasshelby — Mon, 02 Apr 2018 11:22:28 GMT

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

CV Submitted by Bidder

Personal Detail

Is Employee's status in Bidder's Company currently Permanent?

YES

Education Background

Select Secondary Qualification/Education Level

Working Experiences

Total Working Experience

Supporting Documents

Have you submitted documents

YES

Supporting Documents

Have you submitted - Candidate’s Education Certificate

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‌

Re: Help create flags in script

sunny_talwar — Mon, 02 Apr 2018 11:24:30 GMT

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

Re: Help create flags in script

mrthomasshelby — Mon, 02 Apr 2018 11:28:58 GMT

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

sunny_talwar — Mon, 02 Apr 2018 11:30:37 GMT

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 .....;

Re: Help create flags in script

mrthomasshelby — Mon, 02 Apr 2018 11:34:35 GMT

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

sunny_talwar — Mon, 02 Apr 2018 11:35:41 GMT

No problem at all

Re: Help create flags in script

mrthomasshelby — Mon, 02 Apr 2018 11:51:54 GMT

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

sunny_talwar — Mon, 02 Apr 2018 11:55:57 GMT

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

Re: Help create flags in script

mrthomasshelby — Mon, 02 Apr 2018 11:57:54 GMT

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

sunny_talwar — Mon, 02 Apr 2018 12:02:31 GMT

How about this

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

Resident T2_1;

Drop Table T2_1;

Re: Help create flags in script

mrthomasshelby — Mon, 02 Apr 2018 12:09:59 GMT

Still gives the same error. Is it because the field has both text and numbers?

Re: Help create flags in script

sunny_talwar — Mon, 02 Apr 2018 12:11:08 GMT

Can you share the complete script you are running?

Re: Help create flags in script

thevingo — Mon, 02 Apr 2018 12:27:05 GMT

try declaring the field as Dual

Re: Help create flags in script

mrthomasshelby — Mon, 02 Apr 2018 12:27:59 GMT

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;

T2_2:

Load *,

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

Resident T2_1;

Drop Table T2_1;

Re: Help create flags in script

sunny_talwar — Mon, 02 Apr 2018 12:30:31 GMT

May be try 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 Criteria,

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

Resident T2_1

Group By Criteria;

Re: Help create flags in script

mrthomasshelby — Mon, 02 Apr 2018 12:50:41 GMT

I'm not sure if that is what is required Sunny. Basically, Flag15 is defined as 'Pass' if total number of 'CV Submitted by Bidder' is >5 counting from Candidates 1 through Candidate 8 else Fail.

Re: Help create flags in script

sunny_talwar — Mon, 02 Apr 2018 12:56:31 GMT

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;

Re: Help create flags in script

mrthomasshelby — Mon, 02 Apr 2018 13:00:35 GMT

Yes. This was the way to go as I just found out myself! Thanks for your time Sunny! Really really appreciate it!

Re: Help create flags in script

sunny_talwar — Mon, 02 Apr 2018 13:01:34 GMT

No problem

Please close the thread by marking correct and helpful responses.

Best,

Sunny