Removing duplicate values

[vijetas42]

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

[Anil_Babu_Samineni]

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))

[vishsaggi]

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;

[vijetas42]

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.

[vishsaggi]

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?

[vijetas42]

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.

[vishsaggi]

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

[Anonymous]

The field Key (QVD) is correty with join?

[vijetas42]

in Qvd