topic Re: Removing duplicate values in QlikView

Removing duplicate values

vijetas42 — Thu, 16 Nov 2017 13:22:34 GMT

Hi all,

Please suggest I have sample data like ,

code	supplier	doc no	value
BQ1234	v1	1200034215	1000
BQ1234	v1	1200041235	500
BQ1234	v1	2210034215	1000
BQ1234	v2	1200045612	500
BQ1234	v2	2210045612	500
BQ1234	v3	2210045211	300

and I want to check if my last 5 digit of doc no. are same and it's having same value then it should make 0 for value where doc is starting from 12 and should show only value for doc starting with 22.

expected output as below,

code	supplier	doc no	value
BQ1234	v1	1200034215	0
BQ1234	v1	1200041235	500
BQ1234	v1	2210034215	1000
BQ1234	v2	1200045612	0
BQ1234	v2	2210045612	500
BQ1234	v3	2210045211	300

Regards,

Vijeta

Re: Removing duplicate values

Anil_Babu_Samineni — Thu, 16 Nov 2017 13:52:26 GMT

Can you try this?

Sample:

LOAD *, Right([doc no],5) as Right5DocNo Inline [

code, supplier, doc no, value

BQ1234, v1, 1200034215, 1000

BQ1234, v1, 1200041235, 500

BQ1234, v1, 2210034215, 1000

BQ1234, v2, 1200045612, 500

BQ1234, v2, 2210045612, 500

BQ1234, v3, 2210045211, 300

];

Right Join (Sample)

LOAD Right5DocNo, Max(value) as value Resident Sample Group By Right5DocNo Order By Right5DocNo ASC;

Create Chart

Dimensions are - code, supplier, [doc no]

Expression is

Sum(Aggr(Max(value), Right5DocNo))

Re: Removing duplicate values

vishsaggi — Thu, 16 Nov 2017 15:23:50 GMT

May be this?

Tab:

LOAD *, Right(docno, 5) AS Last5doc INLINE [

code, supplier, docno, value

BQ1234, v1, 1200034215, 1000

BQ1234, v1, 1200041235, 500

BQ1234, v1, 2210034215, 1000

BQ1234, v2, 1200045612, 500

BQ1234, v2, 2210045612, 500

BQ1234, v3, 2210045211, 300

];

Right JOIN

LOAD code, supplier, docno, Last5doc, IF(Last5doc = Peek(Last5doc), 0, value) AS value

Resident Tab

Order By value;

Re: Removing duplicate values

vijetas42 — Tue, 28 Nov 2017 16:31:59 GMT

Hi,

Thanks for your reply.above logic is working fine on dummy data but when I incorporate it with actual it takes lot of time to load.Is there any other way arround.

Re: Removing duplicate values

vishsaggi — Tue, 28 Nov 2017 16:55:42 GMT

I am not sure, did you try Anil's script did it take the same time usually doing this at script level should help. May be you want to create a qvd out of this script based on your original data and just load that QVD for your dashboard development may be an option?

Re: Removing duplicate values

vijetas42 — Thu, 22 Mar 2018 12:41:46 GMT

Anil's script didn't worked for me. and I am trying to resolve the same at script level but still did't get any solution.

Re: Removing duplicate values

vishsaggi — Thu, 22 Mar 2018 15:28:41 GMT

Does your actual data is stored in a QVD or just a database source table?

Re: Removing duplicate values

Anonymous — Thu, 22 Mar 2018 15:39:09 GMT

The field Key (QVD) is correty with join?

Re: Removing duplicate values

vijetas42 — Fri, 23 Mar 2018 05:52:25 GMT

in Qvd