Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content
Announcements
Gartner® Magic Quadrant™: 15 YEARS A LEADER - GET THE REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
ashmitp869
Creator II
Creator II

Need assistance in script to get the first record -

Hi there,

Please help me with the script.

SE_TMP:
Load
[Facility Identifier],
"Derived National Service Event Record Id",
"SE.Service Event Record ID",
1 as count_se
1 as count_oos
FROM
QVD;

SE:
NoConcatenate
LOAD DISTINCT
"OOS.Derived National Service Event Record Id" as IDKey,
[Facility Identifier],
"SE.Service Event Record ID",
1 as count_se
0 as count_oos
RESIDENT OOS
WHERE NOT EXISTS ("Derived National Service Event Record Id","OOS.Derived National Service Event Record Id")
;


Concatenate(SE)
LOAD
"Derived National Service Event Record Id" as IDKey,
"Facility Identifier"
,"SE.Service Event Record ID"
,count_se as SE.count_se
,count_moh_se as SE.count_moh_se
RESIDENT SE_TMP;

DROP TABLE SE_TMP;

from the middle script , I am getting two records becoz of different SE.Service Event Record ID .i.e below script

SE:
NoConcatenate
LOAD DISTINCT
"OOS.Derived National Service Event Record Id" as IDKey,
[Facility Identifier],
"SE.Service Event Record ID",
1 as count_se
0 as count_oos
RESIDENT OOS
WHERE NOT EXISTS ("Derived National Service Event Record Id","OOS.Derived National Service Event Record Id")
;

 

Is there any way I get the every first row the the distinct record in RESIDENT table ?

 

 

This is an eg , I have to get the distinct record depending on erived National Service Event Record Id not Service Event Record ID. I can understand why my distinct is not working becoz this are two record  because of Service Event Record ID

Is there any way in qlikview script where I can only pick the first record ?

Labels (2)
3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You can use the LOAD prefix First n to select the first n rows.

First 1

-Rob

ashmitp869
Creator II
Creator II
Author

Hi Rob,

 

First won't work as I have different sets of records ....which i need to show but its different id,Facility Id, Service Repord id...

 

The first 1 will only show the first record of the full data set , but my req is something like below -

How to pick the first record of  each set in qlikview script.

ashmitp869_0-1649800689498.png

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I would think you can just create a new dummy field as you load to use in Not Exists().  For example. 

Data:
LOAD * INLINE [

Id, Name
abc, Rob
abc, Sally
def, John
def, Linda
zzz, Ralph
]
;

Firsties:
LOAD
  *,
  Id as FirstId
Resident Data
Where Not Exists(FirstId, Id)
;

DROP Table Data;
DROP Field FirstId; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com