load the first 4 records of a unique key group - Qlik Community

jjking58 · ‎2018-02-20

Is there a way to load only the first 4 records of a group in the script .

Example : I have a SQL table with multiple customers, but would like to load a maximum of 4 records per customer based on date.

tomasz_tru · ‎2018-02-20

If it's SQL it's better to do it on the database side. Depends on engine: LIMIT with subquery or QUALIFY with OVER.

Tomasz

wdchristensen · ‎2018-02-20

BEGIN /** set up config table to reduce the large where clause **/

DECLARE @TblVar TABLE (tv_PresidentNumber INT, tv_PresidentName NVARCHAR(50), tv_Century INT)

INSERT INTO @TblVar

VALUES

(1, 'George Washington (1789-1797)', 18),

(2, 'John Adams (1797-1801)', 18),

(3, 'Thomas Jefferson (1801-1809)', 19),

(4, 'James Madison (1809-1817)', 19),

(5, 'James Monroe (1817-1825)', 19),

(6, 'John Quincy Adams (1825-1829)', 19),

(7, 'Andrew Jackson (1829-1837)', 19),

(8, 'Martin Van Buren (1837-1841)', 19),

(9, 'William Henry Harrison (1841)', 19),

(10, 'John Tyler (1841-1845)', 19),

(11, 'James K Polk (1845-1849)', 19),

(12, 'Zachary Taylor (1849-1850)', 19),

(13, 'Millard Fillmore (1850-1853)', 19),

(14, 'Franklin Pierce (1853-1857)', 19),

(15, 'James Buchanan (1857-1861)', 19),

(16, 'Abraham Lincoln (1861-1865)', 19),

(17, 'Andrew Johnson (1865-1869)', 19),

(18, 'Ulysses S Grant (1869-1877)', 19),

(19, 'Rutherford B Hayes (1877-1881)', 19),

(20, 'James A Garfield (1881)', 19),

(21, 'Chester Arthur (1881-1885)', 19),

(22, 'Grover Cleveland (1885-1889)', 19),

(23, 'Benjamin Harrison (1889-1893)', 19),

(24, 'Grover Cleveland (1893-1897)', 19),

(25, 'William McKinley (1897-1901)', 19),

(26, 'Theodore Roosevelt (1901-1909)', 20),

(27, 'William Howard Taft (1909-1913)', 20),

(28, 'Woodrow Wilson (1913-1921)', 20),

(29, 'Warren G Harding (1921-1923)', 20),

(30, 'Calvin Coolidge (1923-1929)', 20),

(31, 'Herbert Hoover (1929-1933)', 20),

(32, 'Franklin D Roosevelt (1933-1945)', 20),

(33, 'Harry S Truman (1945-1953)', 20),

(34, 'Dwight D Eisenhower (1953-1961)', 20),

(35, 'John F Kennedy (1961-1963)', 20),

(36, 'Lyndon B Johnson (1963-1969)', 20),

(37, 'Richard Nixon (1969-1974)', 20),

(38, 'Gerald Ford (1974-1977)', 20),

(39, 'Jimmy Carter (1977-1981)', 20),

(40, 'Ronald Reagan (1981-1989)', 20),

(41, 'George Bush (1989-1993)', 20),

(42, 'Bill Clinton (1993-2001)', 20),

(43, 'George W Bush (2001-2009)', 20),

(44, 'Barack Obama (2009-2017)', 20),

(45, 'Donald Trump (2017- )', 20)

END

Select *

FROM

(

SELECT *, ROW_NUMBER() OVER (PARTITION BY tv_Century ORDER BY tv_PresidentNumber) as RankByCentury

FROM @TblVar

) as tv_qry

WHERE RankByCentury <= 4

wdchristensen · ‎2018-02-20

Note that in the 18th century you only have 2 entries since there were not 4 presidents to choose from.

SQL Server 2012 Window Function Basics - Simple Talk

jjking58 · ‎2018-02-20

Thanks William and Tomasz. I will make the adjustments to see which works best

wdchristensen · ‎2018-02-20

The example I showed above is using a windowing function specific to SQL Server (not ANSI SQL). Also, Oracle tends to refer to window functions as analytic functions.