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: 
dunnalahk123
Creator III
Creator III

count expression in back end script

HI,

i am writing below in back end script qlikview.

 

load

count(Id) as project id ,

code

from

xxx.qvd

group by id,code

but i write above i am getting duplicate count  because one project id will have multiple codes so when i group by code project count is duplicating.

ex:

id    code

xxx   code1

xxx   code 2

xxx code 3

so  as project id is same i need count as only 1 not 3. 

so in front end report as well i am using code field so if some one select code as 3 count should be 1 or if some one select code 2 count should be 1 

so how can i write in back end script ?

best regards,

HK

 

 

 

 

1 Solution

Accepted Solutions
dunnalahk123
Creator III
Creator III
Author

hi,

 

i wrote like this and it works perfectly . what i need.

If(Exists([Project ID]), 0, 1)

View solution in original post

12 Replies
goncalo_ricardo_pereira
Partner - Contributor III
Partner - Contributor III

Hi,

Perhaps you can do something like this:

Project:
load
     Id as [project id],
     code
from
xxx.qvd;

ProjectUniqueFlag:
NoConcatenate
load Distinct
     Id as [project id],
     1  as [project id Flag],
from
xxx.qvd;

Or something like this:

Projects:
load
     Id                           as [project id],
     if(Id=Previous(Id),0,1)      as [project id Flag],
     code
from
xxx.qvd
order by Id asc;

Then you just need to Sum the "project id Flag" in your front end.

Best Regards.

Goncalo Pereira

dunnalahk123
Creator III
Creator III
Author

HI Pereira,

thank you for your reply , i tried but still its not working as expected.

 

for your reference i am attaching test qvw file where you can check my script.  

goncalo_ricardo_pereira
Partner - Contributor III
Partner - Contributor III

Hi,

You right, the second option, the if statment doesn't work the way you need.

Have you tried the first option I gave you? If you move the flag to a different table, connected only by the project Id, it should give you what you need and it should remove all the duplications... unless I misunderstood what are you trying to achieve.

 

Project:
load
     Id as [project id],
     code
from
xxx.qvd;

ProjectUniqueFlag:
NoConcatenate
load Distinct
     Id as [project id],
     1  as [project id Flag],
from
xxx.qvd;

 

Example of table:

test.png

Let me know if it make sense or if I misunderstood you.

Best Regards.

Goncalo Pereira

dunnalahk123
Creator III
Creator III
Author

Hi,

sorry i did't get it.

if you dont mind can you keep that script in my sample qvw file and send back me please i will look and test it.

 

best regards,

HK

goncalo_ricardo_pereira
Partner - Contributor III
Partner - Contributor III

Check and try the script

Best Regards

Goncalo Pereira

Kushal_Chawda

can you try using below expression on front end?

=count(distinct Project_ID)

dunnalahk123
Creator III
Creator III
Author

Hi pereira,

 

thank you so much for your support.

 we are very close.

to bring that  [Project ID Flag] field to front end i am trying to store it . but that field is not  storing it 😞

 

NPNUM:
LOAD
// You need the PorjectId here to connecto both tables //
[Project ID] as %KeyProjectId,

// The Project Id to use as a Dimension if necessary //
[Project ID],
MonthNew as Month,
ISO_Country_Code as ISOCode ,


//Count(DISTINCT [Project ID]) as NumValue,
//if([Project ID]=Previous([Project ID]),0,1) as [project id Flag],
Metric,
FY_Month,
MonthName,
FY_Year,
Area_Org as Area,
[Facility Description] as Facility,
Organization,
[Practice],
LES,
IDS,
TS,
OI,
SVT,
SYN,
HPC,
PREM,
GL,
DC,
PCA

FROM
[$(QVD_Path)\GSD_Solution_NPBase.qvd]
(qvd)
where Num='1'

;

// This table will flag all the projects ids and it will remove all the duplications... You just need to Sum the file "[Project ID Flag]" in your front end //
NPNUM_Flag:
NoConcatenate
Load Distinct
%KeyProjectId,
1 as [Project ID Flag]


Resident NPNUM;

 

STORE NPNUM into [$(QVD_Path)\GSD_Solution_LESNPNUMBase.qvd];
Drop table NPNUM;

dunnalahk123
Creator III
Creator III
Author

Hi kush,

 

thank you for your support . i am trying to do it in backed itself. 

Kushal_Chawda

What is the reason to do it in back end?