Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i have the following case in section access:
Access | UserID | Password | RegionID |
UserA | UserA | xx | 01-00-00 |
UserA | UserA | xx | 01-00-01 |
UserA | UserA | xx | 01-00-02 |
UserA | UserA | xx | 01-00-03 |
UserA | UserA | xx | 01-00-04 |
UserA | UserA | xx | 01-00-05 |
UserA | UserA | xx | 01-00-06 |
UserA | UserA | xx | 01-00-07 |
UserA | UserA | xx | 01-00-08 |
UserA | UserA | xx | 01-00-09 |
UserA | UserA | xx | 01-01-00 |
UserA | UserA | xx | 01-01-01 |
UserA | UserA | xx | 01-01-02 |
UserA | UserA | xx | 01-01-03 |
UserA | UserA | xx | 01-01-04 |
UserA | UserA | xx | 01-01-05 |
UserA | UserA | xx | 01-01-06 |
UserA | UserA | xx | 01-01-07 |
UserA | UserA | xx | 01-01-08 |
UserA | UserA | xx | 01-01-09 |
UserA | UserA | xx | 01-02-00 |
UserA | UserA | xx | 01-02-01 |
UserA | UserA | xx | 01-02-02 |
UserA | UserA | xx | 01-02-03 |
UserA | UserA | xx | 09-09-09 |
Is there any way to autogenerate the values of RegionID, since the values are well defined for the same user from 01-00-00 till 09-09-09?
R:
load num(rowno(), '00') as R1 AutoGenerate 9;
join (R) load num(rowno()-1, '00') as R2 AutoGenerate 10;
join (R) load num(rowno()-1, '00') as R3 AutoGenerate 10;
Left join (R) load *, text(R1 & '-' & R2 & '-' & R3) as AllRegion Resident R;
S:
LOAD Distinct
Access,
UserID,
Password
FROM
[https://community.qlik.com/thread/165407]
(html, codepage is 1252, embedded labels, table is @1);
join (S) load AllRegion as Region Resident R;
DROP Table R;
R:
load num(rowno(), '00') as R1 AutoGenerate 9;
join (R) load num(rowno()-1, '00') as R2 AutoGenerate 10;
join (R) load num(rowno()-1, '00') as R3 AutoGenerate 10;
Left join (R) load *, text(R1 & '-' & R2 & '-' & R3) as AllRegion Resident R;
S:
LOAD Distinct
Access,
UserID,
Password
FROM
[https://community.qlik.com/thread/165407]
(html, codepage is 1252, embedded labels, table is @1);
join (S) load AllRegion as Region Resident R;
DROP Table R;
Not related to your question - but you must use REGIONID instead of RegionID if you want to use it for data reduction.
Solved!! Thank you Massimo, and Thank you all guys.