Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
colville
Contributor
Contributor

Load Unique Value based on a specific Row and Column

I'm looking to upload unique Values in which share the same ID and the price were unchanged from 1 week to another.

Example:

Data:

 

IDPriceGroupUpdatedWeek
11b3423
12b3422
23b3323
23b3322
34b3623
35b3622
46b2123
46b2122
52b2823
54b28

22

Expected:

 

IDPriceGroupUpdatedWeek
11b123
12b222
34b523
35b622
52b923
54b1022

For Qlik Sense

1 Solution

Accepted Solutions
Nicole-Smith

Using your example data, load script like this:

Data:

LOAD * INLINE [

    ID, Price, Group, UpdatedWeek

    1, 1b, 34, 23

    1, 2b, 34, 22

    2, 3b, 33, 23

    2, 3b, 33, 22

    3, 4b, 36, 23

    3, 5b, 36, 22

    4, 6b, 21, 23

    4, 6b, 21, 22

    5, 2b, 28, 23

    5, 4b, 28, 22

];


Temp:

LOAD ID, COUNT(DISTINCT Price) AS CountPrice

RESIDENT Data

GROUP BY ID;


INNER JOIN (Data)

LOAD ID

RESIDENT Temp

WHERE CountPrice > 1;


DROP TABLE Temp;

Returns a final table that looks like this:

ID Price Group UpdatedWeek
11b3423
12b3422
34b3623
35b3622
52b2823
54b2822

View solution in original post

1 Reply
Nicole-Smith

Using your example data, load script like this:

Data:

LOAD * INLINE [

    ID, Price, Group, UpdatedWeek

    1, 1b, 34, 23

    1, 2b, 34, 22

    2, 3b, 33, 23

    2, 3b, 33, 22

    3, 4b, 36, 23

    3, 5b, 36, 22

    4, 6b, 21, 23

    4, 6b, 21, 22

    5, 2b, 28, 23

    5, 4b, 28, 22

];


Temp:

LOAD ID, COUNT(DISTINCT Price) AS CountPrice

RESIDENT Data

GROUP BY ID;


INNER JOIN (Data)

LOAD ID

RESIDENT Temp

WHERE CountPrice > 1;


DROP TABLE Temp;

Returns a final table that looks like this:

ID Price Group UpdatedWeek
11b3423
12b3422
34b3623
35b3622
52b2823
54b2822

View solution in original post