Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

DipeshVadgama
Contributor

How to load MAX version of Id

Hi,

I have a data with Id which is combine with version. I want to load only last version of ids.

Example Data

ID

A1234

A1234_1

C5678

C5678_1

C5678_2

D9999

D9999_1

D9999_2

D9999_3

 

Result

A1234_1

C5678_2

D9999_3

Labels (2)
1 Solution

Accepted Solutions

Re: How to load MAX version of Id

Here is another option, but require you to create a qvd layer

TempTable:
LOAD * INLINE [
    ID
    A1234
    A1234_1
    C5678
    C5678_1
    C5678_2
    D9999
    D9999_1
    D9999_2
    D9999_3
];

Table:
LOAD ID,
	 SubField(ID & '_', '_', 1) as IDTemp
Resident TempTable
Order By ID desc;

DROP Table TempTable;

STORE Table into Table.qvd (qvd);
DROP Table Table;

FinalTable:
LOAD ID,
	 IDTemp
FROM Table.qvd (qvd)
Where not Exists(IDTemp);
9 Replies
Channa
Contributor

Re: How to load MAX version of Id

load where ID like '%_%'

 

if you want to load only MAX ID try to use SUBField and get only Max(Number)

Channa
OmarBenSalem
Esteemed Contributor

Re: How to load MAX version of Id

t0:
load ID, subfield(ID,'_',1) as First,subfield(ID,'_',2) as second Inline [
ID

A1234

A1234_1

C5678

C5678_1

C5678_2

D9999

D9999_1

D9999_2

D9999_3
];

NoConcatenate
final:
load First as ID resident t0 where len(trim(second))=0;
Concatenate(final)
load First&'_'&max(second) as ID Resident t0 group by First;

drop table t0;

 

result:

Capture.PNG

pradosh_thakur
Honored Contributor II

Re: How to load MAX version of Id

load ID & '_' &  MAX(MAX_NUM) as ID
group by ID;
load subfield(ID,'_',2) AS MAX_NUM ,subfield(ID,'_',1) AS ID inline  [
ID

A1234

A1234_1

C5678

C5678_1

C5678_2

D9999

D9999_1

D9999_2

D9999_3
];
Learning never stops.
Highlighted

Re: How to load MAX version of Id

This should do the trick:

LOAD
    FirstSortedValue(ID, -Version) as ID
GROUP BY
    BaseID;
LOAD
    ID, 
    SubField(ID,'_',1) as BaseID, 
    Alt(SubField(ID,'_',2),0) as Version
FROM 
    ...source...
;

talk is cheap, supply exceeds demand

Re: How to load MAX version of Id

Another option

Table:
LOAD SubField(ID & '_', '_', 1) as IDTemp,
	 MaxString(ID) as ID
Group By  SubField(ID & '_', '_', 1);
LOAD * INLINE [
    ID
    A1234
    A1234_1
    C5678
    C5678_1
    C5678_2
    D9999
    D9999_1
    D9999_2
    D9999_3
];

DROP Field IDTemp;
DipeshVadgama
Contributor

Re: How to load MAX version of Id

Hi All,

I have applied your scripts which do works. But when I apply same script on Hugh file it take a lot of time to get load.

It had a lot of other fields, Any suggestion.

 

Re: How to load MAX version of Id

Here is another option, but require you to create a qvd layer

TempTable:
LOAD * INLINE [
    ID
    A1234
    A1234_1
    C5678
    C5678_1
    C5678_2
    D9999
    D9999_1
    D9999_2
    D9999_3
];

Table:
LOAD ID,
	 SubField(ID & '_', '_', 1) as IDTemp
Resident TempTable
Order By ID desc;

DROP Table TempTable;

STORE Table into Table.qvd (qvd);
DROP Table Table;

FinalTable:
LOAD ID,
	 IDTemp
FROM Table.qvd (qvd)
Where not Exists(IDTemp);
DipeshVadgama
Contributor

Re: How to load MAX version of Id

Hi, Thank you it work. But just one last thing. Currently it load max _numbers, but if any ID do not have _ it also has to added.

Re: How to load MAX version of Id

What exactly do you mean? Do you mean if your data was like this

TempTable:
LOAD * INLINE [
    ID
    A1234
    C5678
    C5678_1
    C5678_2
    D9999
    D9999_1
    D9999_2
    D9999_3
];

You would still want A1234 to load as the max value? My script seems to do that already.

Community Browser