topic Re: flag in QlikView

flag

satishqlik — Wed, 25 Nov 2020 16:16:04 GMT

Hi Viewers,

how to create flage for

same employees(emp id) which having different doj

Any help will be appreciated!

Re: flag

micheledenardi — Tue, 06 Jun 2017 06:56:16 GMT

What is doj ?

Can you give us a better view of your data model/structure ?

Re: flag

Anil_Babu_Samineni — Tue, 06 Jun 2017 06:57:29 GMT

You have to explain little more with data

Re: flag

satishqlik — Tue, 06 Jun 2017 06:57:37 GMT

Date of joining

Re: flag

avinashelite — Tue, 06 Jun 2017 06:58:52 GMT

Explain with a sample data set

Re: flag

micheledenardi — Tue, 06 Jun 2017 06:59:57 GMT

Different "doj" compared to what ?

As loveisfail told, we need more details regarding your table structure.

Re: flag

satishqlik — Tue, 06 Jun 2017 07:03:33 GMT

I have a table with employee id and date of joining

I want to count employee who are having different doj

Let me take

Emp id 1 has two different doj's.

For this I want to create flag

How to accomplish this?

Re: flag

satishqlik — Tue, 06 Jun 2017 07:04:44 GMT

Hi Avinash,

I have a table with employee id and date of joining

I want to count employee who are having different doj

Let me take

Emp id 1 has two different doj's.

For this I want to create flag

How to accomplish this?

Re: flag

satishqlik — Tue, 06 Jun 2017 07:09:25 GMT

I have a table with employee id and date of joining

I want to count employee who are having different doj

Let me take

Emp id 1 has two different doj's.

For this I want to create flag

ultimately I want to count same employees who are having different DOJ.

How to accomplish this?

Re: flag

tresB — Tue, 06 Jun 2017 07:12:38 GMT

Try like:

Input:

Load

EmpID,

DOJ

From <>:

Join

Load

EmpID,

Count(Distinct DOJ) as Count

Resident Input Group By EmpID;

Final:

Load

If( Count>1, 1,0) as Flag

Resident Input;

Drop Table Input;

Re: flag

avinashelite — Tue, 06 Jun 2017 07:13:03 GMT

Check the sample app attached

Re: flag

micheledenardi — Tue, 06 Jun 2017 08:04:20 GMT

MainTable_tmp:

LOAD EmpID,

Doj

FROM

[Cartel1.xlsx] (ooxml, embedded labels, table is Foglio1);

left join(MainTable_tmp)

load

EmpID,

count( distinct Doj) as NumOfDoj

Resident MainTable_tmp

group by EmpID;

MainTable:

NoConcatenate

load

EmpID,

Doj,

NumOfDoj,

if(NumOfDoj>1,1,0) as FlagMultipleDoj

Resident MainTable_tmp;

drop Table MainTable_tmp;

Re: flag

satishqlik — Tue, 06 Jun 2017 10:33:33 GMT

Thanks for all your suggestions and sorry for my late response.

Still I have been suffering on the same.

I have tried all the ways as you suggested

but still not getting accurate results.

Can you look at my application and suggest where I did wrong?

PFA for your reference!!!

Pls help!!!

Re: flag

satishqlik — Tue, 06 Jun 2017 10:35:34 GMT

Hi Avinash,

can you look @ my application which I shared in the above!!

Re: flag

avinashelite — Tue, 06 Jun 2017 10:40:27 GMT

Try this script

t1:

LOAD id,

Date(doj,'DD-MMM-YY') as doj,

type

FROM

(ooxml, embedded labels, table is May);

LOAD id,

Date(doj,'DD-MMM-YY') as doj,

type

FROM

(ooxml, embedded labels, table is Jun);

left join

LOAD

id,

Count(DISTINCT doj) as count

Resident t1

Group By id;

final:

NoConcatenate

LOAD *,

If(count>1,1,0) as flag1

Resident temp;

DROP Table t1;;

Re: flag

satishqlik — Tue, 06 Jun 2017 10:50:53 GMT

Hi Avinash,

Thanks for your quick response!!

Still facing same problem on count in which we have created with

Count(distinct doj) is giving us non-exact results.

Anymore suggestions???

Re: flag

avinashelite — Tue, 06 Jun 2017 10:54:40 GMT

Count(distinct doj) is giving us non-exact results?

could you explain what your excepting ?

Re: flag

tresB — Tue, 06 Jun 2017 10:55:12 GMT

What is your expected output?

Re: flag

satishqlik — Tue, 06 Jun 2017 11:00:28 GMT

Yes avinash,

only id 924 is getting accurate result count(Distinct doj) is 2(2 different doj for same id)

But rest of the id's having same doj's so the result should be 1(count for id 85041, it should be 1)

PFA snapshot for your reference!!!

Re: flag

satishqlik — Tue, 06 Jun 2017 11:03:45 GMT

Hi Tresesco,

Kindly look@ my application

count(distinct doj) is not giving exact result

Please suggest where I did wrong.?