topic Re: extract function in Qlik in QlikView

extract function in Qlik

master_student — Thu, 09 Feb 2017 08:13:51 GMT

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

Re: extract function in Qlik

Anonymous — Thu, 09 Feb 2017 08:18:14 GMT

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?

Re: extract function in Qlik

master_student — Thu, 09 Feb 2017 08:28:55 GMT

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

Re: extract function in Qlik

Anonymous — Thu, 09 Feb 2017 08:30:13 GMT

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
];

Re: extract function in Qlik

master_student — Thu, 09 Feb 2017 08:32:33 GMT

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

Re: extract function in Qlik

Thu, 09 Feb 2017 08:49:29 GMT

Try like this

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

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

Re: extract function in Qlik

jonathandienst — Thu, 09 Feb 2017 08:55:32 GMT

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);

LOAD * FROM database table

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

Re: extract function in Qlik

Anonymous — Thu, 09 Feb 2017 09:09:01 GMT

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;

Re: extract function in Qlik

Anonymous — Thu, 09 Feb 2017 09:10:09 GMT

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

Re: extract function in Qlik

master_student — Thu, 09 Feb 2017 10:41:23 GMT

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