Split record after dot and check for unique field and if not then get latest value?

[worktrans]

I am loading data into my project, only I have a problem with checking for a unique field.
I have a field named "Number" and this column has a number it in and most of the time the last two numbers end with 01 but sometimes it is 02 or 03 or 04 etc. For example the records will look like this:

123456789.01
111111111.01
222222222.01
987654321.01
987654321.02
555555555.01
555555555.02
555555555.03


What I want to do with this field is checking if the number before the dot for example "111111111" is unique, and if so then get the full number like "111111111.01". But when the number before the dot is not unique like in the above example "555555555". I want to check for the two number after the dot that it will look at the latest number so that the outcome would be "555555555.03".

Is this possible inside the Qlik editor? And if so how would I do this?

[Vegar]

Try something like this. 

load Subfield (Number,'.',1) &'.'& max(Subfield (Number,'.',2) ) as Decimal

FROM Source

Group By Subfield (Number,'.',1)

[worktrans]

When I add the line:
Subfield (Number,'.',1) &'.'& max(Subfield (Number,'.',2) ) as Decimal

Under the LOAD and load the data I get the error:
"Invalid expression"

When I remove the above line the invalid expression error is gone..

[Vegar]

Try this code. It will give you the desired result. Both the expression and the group by are important for this solution to work.

LOAD
Subfield (Number,'.',1) &'.'& max(Subfield (Number,'.',2) ) as Number
Inline [
Number
123456789.01
111111111.01
222222222.01
987654321.01
987654321.02
555555555.01
555555555.02
555555555.03]
Group By Subfield (Number,'.',1)
;

Could it be that you left out something important when you described your problem? 

[worktrans]

Hello Vegar,

This is the real query that I have running, and in here the name "Nummer" is the field I described above in this topic.

Concatenate(Profit)
LOAD
	Administratie & '|' & Verkooprelatienummer						AS %KlantKey,
	Administratie & '|' & Verkooprelatie							AS %KlantKeyNm,
	FLOOR(NUM(Date("Factuurdatum")))								AS %DatumKey,    
	Administratie													AS %AdministratieKey,
    ReferentieJaar,
	Periode															AS [Ref. periode],
    Factuurdatum,	
	KeepChar("WeekNr", '0123456789')								AS [WeekNr],
    Medewerkernummer												AS [Werkn code],
    Medewerker														AS [Naam werknemer],
    Nummer,   

	IF( Vestigingnummer = 1110, 1,
	  IF( Vestigingnummer = 1120, 2,
		IF( Vestigingnummer = 1230, 3,
		  IF( Vestigingnummer = 1240, 4,
			IF( Vestigingnummer = 1150, 5,
			  IF( Vestigingnummer = 1260, 6,
				IF( Vestigingnummer = 1170, 7,
				  IF( Vestigingnummer = 9000, 99 , 0))))))))))	AS [Vestiging Nr],
	Vestiging														AS [Vestiging Omschrijving]
FROM [lib://Loader/Export.qvd]
(qvd)

And as described when I change the line:
Nummer,
to:
Subfield (Nummer,'.',1) &'.'& max(Subfield (Nummer,'.',2) ) as Decimal

And add at the bottom of the query:
Group By Subfield (Nummer,'.',1)

I get the described error message.

What am I doing wrong in the query?

[MayilVahanan]

HI

If you want to keep only the max values in the load script, try like below

Inner Join(Profit)
Load Subfield (Nummer,'.',1) &'.'& Num(max(Subfield (Nummer,'.',2)),'00') as Nummer
Resident Profit
Group by Subfield (Nummer,'.',1);

Suppose, you want to restrict in front end but need to bring all values in the script, then try like below

Left Join(Profit)
Load Subfield (Nummer,'.',1) &'.'& Num(max(Subfield (Nummer,'.',2)),'00') as Nummer, 1 as NummerFlag
Resident Profit
Group by Subfield (Nummer,'.',1);

Use "NummerFlag" in the front end wherever its required