Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi,
i wrote like this and it works perfectly . what i need.
If(Exists([Project ID]), 0, 1)
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
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.
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:
Let me know if it make sense or if I misunderstood you.
Best Regards.
Goncalo Pereira
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
Check and try the script
Best Regards
Goncalo Pereira
can you try using below expression on front end?
=count(distinct Project_ID)
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;
Hi kush,
thank you for your support . i am trying to do it in backed itself.
What is the reason to do it in back end?