Load Unique Value based on a specific Row and Column

[Anonymous]

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:

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

Expected:

ID	Price	Group	UpdatedWeek
1	1b	1	23
1	2b	2	22
3	4b	5	23
3	5b	6	22
5	2b	9	23
5	4b	10	22

For Qlik Sense

[Nicole-Smith · Accepted solution]

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
1	1b	34	23
1	2b	34	22
3	4b	36	23
3	5b	36	22
5	2b	28	23
5	4b	28	22