Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Against each date(MM/DD/YYYY) i have month value as (YYYYMM). Since I am not using the date in the dimension it is showing duplicate value which is same as many dates in the month. So to avoid this duplication i want to get rid of the duplicates in the dimension. Please help me to write the calculated expression for the same.
I think what is happening here is a dimensions problem.You need to resolve this within your load query.
1. You need to declare the YYYYMM field in your as month. e.g =Month(yyyymm) i.e =Month(month year filed name).
2. The problem ith having it as is inside of your query is that it is just a number and means nothing. You need to declare what it is in your load script first. Think of your report as a fruit salad and a tomato is the yearmonth field. You cannot just chuck it in. You would have to change it into a guava or something before you include it.
Your result is because you put the tomato in along with the apples and the rest.
Send us an example if I am wrong .
Thanks,
can you please share expected O/P
Currently the O/P is (Since we use date range in the sales expression there is duplicates)
State City City ID Month Sales
A B B1 201601 123
A B B1 201601 123
A B B1 201601 123
A B B1 201601 123
A B B1 201601 123
.
.
.
Expected O/P
State City City ID Month Sales
A B B1 201601 123
Any one has any idea how this can be achieved?
Maybe like
Table:
LOAD
Field1 & Field2 as Key,
Field1,
Field2,
Field3
FROM ....
WHERE NOT EXISTS(Key, Field1 & Field2);
DROP FIELD Key;
Only first occurence of Key value will be loaded into the resident table. Field3 is not considered for distinctness.
Thanks for your response.
The repeatation what is see in the chart is not because of the data. It is because as i mentioned the expressions contain date range. Due to which there is a repeatation. Hope you got the issue. If not please let me know i will explain further.
I think what is happening here is a dimensions problem.You need to resolve this within your load query.
1. You need to declare the YYYYMM field in your as month. e.g =Month(yyyymm) i.e =Month(month year filed name).
2. The problem ith having it as is inside of your query is that it is just a number and means nothing. You need to declare what it is in your load script first. Think of your report as a fruit salad and a tomato is the yearmonth field. You cannot just chuck it in. You would have to change it into a guava or something before you include it.
Your result is because you put the tomato in along with the apples and the rest.
Send us an example if I am wrong .
Thanks,
Hi Rikab,
May be
[MonthValue] - dimension,
Aggr(Sum(Volume), [MonthValue]) - expression?
Regards,
Andrey
Sorry this didn't work out.
can you please post your app so that we can look where the issue is