Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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');
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?
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
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
];
no, it isn't always start with dir..
Try like this
where PERSON_ID=subfield(sup,'\',-1)
or
where PERSON_ID=purgechar(sup,'abcdefgh...xyz_@%')
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');
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;
That should give you the first occurance of a SUP that contains the PERSON_ID when sorted by the load order.
Thanks a lot. will try it, and let you know