Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please refer the below table, we have Item ID , color status and Date (weekly snapshot Date) my scenario is latest color status check previously changed color status and there is change means need to compare two dates and provide number of days difference
Latest Color = Yellow 5/8/2023
Previous Color = Red 4/21/2023
EX: Max(5/8/2023) - Min(Red4/21/2023 ) = 17 Days difference
Kindly help to achieve this in Qliksense script
Thanks in Advance
Item ID | Color Status | Date |
10056328 | Unassigned | 3/10/2023 |
10056328 | Unassigned | 3/17/2023 |
10056328 | Yellow | 3/24/2023 |
10056328 | Yellow | 3/31/2023 |
10056328 | Red | 4/7/2023 |
10056328 | Red | 4/14/2023 |
10056328 | Red | 4/21/2023 |
10056328 | Yellow | 4/28/2023 |
10056328 | Yellow | 5/5/2023 |
10056328 | Yellow | 5/8/2023 |
Hi @Siraj,
I've just tried to load same table and rows than you and I managed to get the number of days when color is changed with Peek function (https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFunc...)
My script below:
RawData:
LOAD * INLINE [
Item ID, Color Status, Date
10056328, Unassigned, 3/10/2023
10056328, Unassigned, 3/17/2023
10056328, Yellow, 3/24/2023
10056328, Yellow, 3/31/2023
10056328, Red, 4/7/2023
10056328, Red, 4/14/2023
10056328, Red, 4/21/2023
10056328, Yellow, 4/28/2023
10056328, Yellow, 5/5/2023
10056328, Yellow, 5/8/2023
];
TempTable:
LOAD
[Item ID],
[Color Status],
[Date],
If([Color Status] <> Peek([Color Status]) and [Item ID] = Peek([Item ID]), [Date]) as ColorChangeDate
Resident RawData
Order By [Item ID], [Date];
FinalTable:
LOAD
[Item ID],
[Color Status],
[Date],
[ColorChangeDate],
If([Item ID] = Peek([Item ID]) and [Color Status] = Peek([Color Status]), [Date] - Peek([ColorChangeDate])) as DateDifference
Resident TempTable
Order By [Item ID], [Date];
DROP Table TempTable;
DROP Table RawData;
Regards,
Benoit
Hi @sirine93,
Can you please check if below similar conversation is helpful ?
https://community.qlik.com/t5/New-to-Qlik-Sense/Difference-between-date/td-p/1788764
Regards,
Benoit
Hi Benoit,
Thanks for your reply,
but this is not solution for my Scenario. I need to compare two colors (Previous value) in same column based on changes in color I need to find Date difference.
Hi @Siraj,
I've just tried to load same table and rows than you and I managed to get the number of days when color is changed with Peek function (https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFunc...)
My script below:
RawData:
LOAD * INLINE [
Item ID, Color Status, Date
10056328, Unassigned, 3/10/2023
10056328, Unassigned, 3/17/2023
10056328, Yellow, 3/24/2023
10056328, Yellow, 3/31/2023
10056328, Red, 4/7/2023
10056328, Red, 4/14/2023
10056328, Red, 4/21/2023
10056328, Yellow, 4/28/2023
10056328, Yellow, 5/5/2023
10056328, Yellow, 5/8/2023
];
TempTable:
LOAD
[Item ID],
[Color Status],
[Date],
If([Color Status] <> Peek([Color Status]) and [Item ID] = Peek([Item ID]), [Date]) as ColorChangeDate
Resident RawData
Order By [Item ID], [Date];
FinalTable:
LOAD
[Item ID],
[Color Status],
[Date],
[ColorChangeDate],
If([Item ID] = Peek([Item ID]) and [Color Status] = Peek([Color Status]), [Date] - Peek([ColorChangeDate])) as DateDifference
Resident TempTable
Order By [Item ID], [Date];
DROP Table TempTable;
DROP Table RawData;
Regards,
Benoit