Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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;
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.
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?
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.
Does your actual data is stored in a QVD or just a database source table?
The field Key (QVD) is correty with join?
in Qvd