Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

colville
New 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

Re: Load Unique Value based on a specific Row and Column

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
1 Reply

Re: Load Unique Value based on a specific Row and Column

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