Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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 |