extract function in Qlik

[master_student]

Hello Guys,

I hope you're great

I had a script that i entred manually, but now i would like to get from my database because I have all fields in the Data base

here's a part of my script :

LOAD * INLINE

   [ID,SUP

329,dir-329

]

here's the fields in my data base :

the problem is that I get many rows whene I filter the person_id='329'

is that any function in Qlik that allow me to get only the row dir-329 ; when the PERSON_ID = the number in the SUP field?

Thanks for your valuable help

[Anonymous]

Will the required/correct SUP always be the last entry for the PERSION_ID

or will any of them be ok but just not duplicates?

[master_student]

Hello Deran,

I need the first row that has the same number of the person_id when sorting from the first row to the last, it could be dir or any other Hierarchy

[Anonymous]

Will it always start with dir-  ?

Data:
Load *
where Len(SUP) >= 1;;
Load Distinct
if(WildMatch(SUP,'dir-*'),PERSON_ID) as PERSON_ID,
if(WildMatch(SUP,'dir-*'),SUP) as SUP;
LOAD * INLINE [
PERSON_ID, SUP
329, dg-133028
329, chief-135
329, dir-329
328, dg-133028
328, chief-135
328, dir-328
327, dg-133028
327, chief-135
327, dir-327
326, dg-133028
326, chief-135
326, dir-326
];

[master_student]

no, it isn't always start with dir..

[#6]

Try like this

where PERSON_ID=subfield(sup,'\',-1)

or

where PERSON_ID=purgechar(sup,'abcdefgh...xyz_@%')

[jonathandienst · Accepted solution]

So you want to load the rows where the number part of the SUB field matches the PERSON_ID?

Then this will do it:

LOAD * FROM database table

WHERE PERSON_ID = Subfield(SUP, '-', -1);

Or

LOAD * FROM database table

WHERE PERSON_ID = KeepChar(SUP, '0123456789');

[Anonymous]

Try this

Data: // Your Data with these two fields loads into this table....
LOAD * INLINE [
PERSON_ID, SUP
329, dg-133028
329, chief-135
329, dir-329
328, dg-133028
328, chief-328
328, xyz-328
328, dir-328
327, dg-133028
327, chief-135
327, dir-327
326, dg-133028
326, chief-135
326, dir-326
];


// Get only the rows that contaion PERSON_ID in the SUP and add RowNo
Temp:
NoConcatenate
Load RowNo() as Row,
*
where num#(PERSON_ID) = num#(SUP_NO);
Load
PERSON_ID,
SubField(SUP,'-',2) as SUP_NO,
SUP
Resident Data; 

drop Table Data; 

// Get the Min RowNo / First occurance of the SUP
Temp2:
NoConcatenate
Load min(Row) as Row,
PERSON_ID
Resident Temp
Group by  
PERSON_ID;

// Left Join the above to remove the records we dont need
Left Join(Temp2)
load * Resident Temp;

Drop table Temp;

exit Script;

[Anonymous]

That should give you the first occurance of a SUP that contains the PERSON_ID when sorted by the load order.

[master_student]

Thanks a lot. will try it, and let you know