Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Please help me out
i am not able to find the solution for my scenario
Please find the explanation
Input:-
Member ID | Country | YearMonth |
1000 | Ireland | 2001Mar |
1001 | Ireland | 2012Aug |
1002 | Ireland | 2012Aug |
1003 | Ireland | 2014Dec |
1004 | Ireland | 2013Jan |
1005 | Netherland | 2012Mar |
1006 | Netherland | 2013Mar |
1007 | India | 2013Dec |
i have three input field Unique "Member ID" ,Repeated "Country" values and YearMonth.
I want to get Countruy name for each member id with the help of YearMonth.
for the unique name i want to add serial number in the last of the country name which start from '01' always but on the basis of Year Month.
Means whenever the new country name will come then i want restart the sequence number and if same YearMonth comes in front of same country then show the same Serial No. as above in output field.
Should show the Sorting of Serial No. on the basis YearMonth Asc Order as in my data data YearMonth are shuffle.
Please find the below table for better understanding.
Output Table:-
Member ID | Country | YearMonth | Output |
1000 | Ireland | 2001Mar | Ireland01 |
1001 | Ireland | 2012Aug | Ireland02 |
1002 | Ireland | 2012Aug | Ireland02 |
1003 | Ireland | 2014Dec | Ireland04 |
1004 | Ireland | 2013Jan | Ireland03 |
1005 | Netherland | 2012Mar | Netherland01 |
1006 | Netherland | 2013Mar | Netherland02 |
1007 | India | 2013Dec | India01 |
Hi,
Check this Script
T1:
Load [Member ID],Country,Date#(YearMonth,'YYYYMMM') as YearMonth;
LOAD * INLINE [
Member ID, Country, YearMonth
1000, Ireland, 2001Mar
1001, Ireland, 2012Aug
1002, Ireland, 2012Aug
1003, Ireland, 2014Dec
1004, Ireland, 2013Jan
1005, Netherland, 2012Mar
1006, Netherland, 2013Mar
1007, India, 2013Dec
];
NoConcatenate
T2:
Load *,Country&Num(AutoNumber(YearMonth&Country,Country),'#00') as OutPut Resident T1 Order by YearMonth;
DROP Table T1;
Result:
Hi,
Check this Script
T1:
Load [Member ID],Country,Date#(YearMonth,'YYYYMMM') as YearMonth;
LOAD * INLINE [
Member ID, Country, YearMonth
1000, Ireland, 2001Mar
1001, Ireland, 2012Aug
1002, Ireland, 2012Aug
1003, Ireland, 2014Dec
1004, Ireland, 2013Jan
1005, Netherland, 2012Mar
1006, Netherland, 2013Mar
1007, India, 2013Dec
];
NoConcatenate
T2:
Load *,Country&Num(AutoNumber(YearMonth&Country,Country),'#00') as OutPut Resident T1 Order by YearMonth;
DROP Table T1;
Result:
Hi Swati,
It looks to me that not only is Settu's reply helpful but it is correct and should be marked as such.
Hi Settu,
Thank you so much for your reply..
Its working F9..
I am facing one more issue in my application...
If you can help me in that ,i will be very grateful...
I am Trying to do cumulation with Multiple Dimension which show 5months data on each bar...
I tried ,but stuck on one point ..
So for referance i am attaching the qvw here..
In Qvw you can see ,I select one Brand i.e. 'A' ..
Please check Double Dimension Chart First bar : It should show the data of 5 months from Sep12-July12 but it is showing data from Mar12-Jan13, which is wrong....As Oct,12,Nov12,Dec12 has no data....
I want that each bar should show 5 months data only,in case some months has its data or not..
Hey hi Andrew,
I know Settu 's reply is correct..
But my discussion was not over with Settu so i marked as helpful..
Now you can check i marked it correct...
Hi,
In Qvw you can see ,I select one Brand i.e. 'A' ..
Please check Double Dimension Chart First bar : It should show the data of 5 months from Sep12-July12 but it is showing data from Mar12-Jan13, which is wrong....As Oct,12,Nov12,Dec12 has no data....
I want that each bar should show 5 months data only,in case some months has its data or not..
When I Select Brand 'A', I see the Sep12 - Jan13 Data. (you mentioned Mar12-Jan13)
I think, the Actual sum value from Mar12 - Jan13.
I don't have an idea how to display that range in the dimension, Because Your expression says the Date Range as >=Jan-13 and <=Dec-13.
When we put the addmonths(MonthYear,-4) in the Dimension-> This will actually goes Previous 4 month. This won't check Possible value of not.
Note:
Regarding your data model,
I Suggest you to Just Link the Cal_Date from the Sales Table with your Master Calendar. You linked Month, Year and day.
I modified the script little bit. You can use, if it suits you..
Create a Other Thread for the calculated Dimension range Month problem. you will get More suggestions.
Hey hi,
I have one more question related to this logic..
Want some Addition..it is little different from above
Ex if Country Ireland has no data in YearMonth 2012Oct 2012Nov 2013Feb then also I want the unique number with blank or 0 Sales ..
Same with Netherland and India
Other thing will be same as ealier mentioned
Input:-
Member ID | Country | YearMonth | Sales |
1000 | Ireland | 2012Sep | 10 |
1001 | Ireland | 2012Dec | 20 |
1002 | Ireland | 2012Dec | 30 |
1003 | Ireland | 2013Mar | 30 |
1004 | Ireland | 2013Jan | 40 |
1005 | Netherland | 2012Nov | 11 |
1006 | Netherland | 2013Feb | 100 |
1007 | India | 2013Dec | 5 |
Output Table:-
Country | Output | Sales |
Ireland | Ireland01 | 10 |
Ireland | Ireland02 | - |
Ireland | Ireland03 | - |
Ireland | Ireland04 | 20 |
Ireland | Ireland04 | 30 |
Ireland | Ireland05 | 40 |
Ireland | Ireland06 | - |
Ireland | Ireland07 | 30 |
Country | Output | Sales |
Netherland | Ireland01 | 11 |
Netherland | Ireland02 | - |
Netherland | Ireland03 | - |
Netherland | Ireland04 | 100 |