Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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
Highlighted
Creator II
Creator II

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
Highlighted

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

Highlighted
Creator II
Creator II

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.  

Highlighted

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

Highlighted
Creator II
Creator II

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

Highlighted

Check and try the script

Best Regards

Goncalo Pereira

Highlighted
MVP
MVP

can you try using below expression on front end?

=count(distinct Project_ID)

Highlighted
Creator II
Creator II

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;

Highlighted
Creator II
Creator II

Hi kush,

 

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

MVP
MVP

What is the reason to do it in back end?