6 Replies Latest reply: Jan 28, 2016 4:20 AM by Swati Tomar RSS

    How to create unique Id

    Swati Tomar

      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
      1000Ireland 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

      YearMonthOutput
      1000 Ireland 2001MarIreland01

      1001

      Ireland

      2012AugIreland02

      1002

      Ireland

      2012AugIreland02

      1003

      Ireland

      2014DecIreland04

      1004

      Ireland

      2013JanIreland03

      1005

      Netherland

      2012MarNetherland01

      1006

      Netherland

      2013MarNetherland02

      1007

      India

      2013DecIndia01
        • Re: How to create unique Id
          Settu Periyasamy

          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:

          Capture.JPG

            • Re: How to create unique Id
              Swati Tomar

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

                • Re: How to create unique Id
                  Settu Periyasamy

                  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.

              • Re: How to create unique Id
                Andrew Walker

                Hi Swati,

                It looks to me that not only is Settu's reply helpful but it is correct and should be marked as such.

                • Re: How to create unique Id
                  Swati Tomar

                  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

                  YearMonthSales
                  1000Ireland 2012Sep10

                  1001

                  Ireland

                  2012Dec20

                  1002

                  Ireland

                  2012Dec30

                  1003

                  Ireland

                  2013Mar30

                  1004

                  Ireland

                  2013Jan40

                  1005

                  Netherland

                  2012Nov11

                  1006

                  Netherland

                  2013Feb100

                  1007

                  India

                  2013Dec5

                   

                   

                  Output Table:-

                  Country

                  OutputSales
                  IrelandIreland0110

                  Ireland

                  Ireland02-

                  Ireland

                  Ireland03-

                  Ireland

                  Ireland0420

                  Ireland

                  Ireland0430
                  IrelandIreland0540
                  IrelandIreland06-
                  IrelandIreland07

                  30

                   

                  Country

                  OutputSales
                  NetherlandIreland0111

                  Netherland

                  Ireland02-

                  Netherland

                  Ireland03-
                  NetherlandIreland04100