Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

swatitomar
New Contributor III

How to create unique Id

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
1 Solution

Accepted Solutions

Re: How to create unique Id

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

6 Replies

Re: How to create unique Id

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

effinty2112
Honored Contributor

Re: How to create unique Id

Hi Swati,

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

swatitomar
New Contributor III

Re: How to create unique Id

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

swatitomar
New Contributor III

Re: How to create unique Id

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

Re: How to create unique Id

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.

swatitomar
New Contributor III

Re: How to create unique Id

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
Community Browser