topic Re: How to create unique Id in QlikView

How to create unique Id

swatitomar — Mon, 07 Dec 2015 10:24:58 GMT

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

Re: How to create unique Id

settu_periasamy — Tue, 08 Dec 2015 02:56:13 GMT

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:

Re: How to create unique Id

effinty2112 — Tue, 08 Dec 2015 09:19:08 GMT

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

swatitomar — Tue, 08 Dec 2015 09:28:57 GMT

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

swatitomar — Tue, 08 Dec 2015 09:32:17 GMT

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

settu_periasamy — Wed, 09 Dec 2015 05:11:41 GMT

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

swatitomar — Thu, 28 Jan 2016 09:20:46 GMT

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