Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear ALL,
I have the following data and I needs to calculate Start_Date and End_Date from Update date.
Input:
Update date | Talisma Name | Team Name | TL Name |
31-Jan-16 | Naresh Medicala | The Achievers | Krunal Panchal |
1-Feb-16 | Naresh Medicala | Silver Hawks | Sumeet Chothani |
22-Apr-16 | Naresh Medicala | The Victorious | Hemant Mohapatra |
Output:
Start_date End_Date TalismaName TL_Name
31-Jan-16 31-Jan-16 Naresh KrunalPunchal
1-Feb-16 21-Apr-16 Naresh SummetChothani
22-Apr-16 11-May-16(Today()-1) Naresh HemantMohapatra
The logic behind the calculation is Update_date should get consider as Start_Date but for End_Date should get populate on the basis of nextdate-1 of Update_date field as wrote in my output.
Can anybody tell me how i can build the logic for the Same dynamically.
Help / Suggestion would be much appreciated if any !!
gwassenaar , hic , jagan PLease comment
Regards
Sarfaraz
Something like this:
Data:
LOAD
[Update date],
[Talisma Name],
[Team Name],
[TL Name]
FROM ....;
Data2:
LOAD Date([Update date]) as Start_date,
Date(Alt(Previous([Update date]), 0) - 1) as End_date,
[Talisma Name],
[Team Name],
[TL Name]
Resident Data
Order By [Update date] DESC;
DROP Table Data;
Try this:
Table:
LOAD [Update date],
[Talisma Name],
[Team Name],
[TL Name]
FROM
[https://community.qlik.com/thread/216608]
(html, codepage is 1252, embedded labels, table is @1);
FinalTable:
LOAD [Update date] AS Start_Date,
Date(Alt(Previous([Update date]), Today()) - 1) as End_Date,
[Talisma Name],
[Team Name],
[TL Name]
Resident Table
Order By [Update date] desc;
DROP Table Table;
If you can sort the initial load (eg from SQL) then you don't need the resident load:
LOAD Date([Update date]) as Start_date,
Date(Alt(Previous([Update date]), 0) - 1) as End_date
[Talisma Name],
[Team Name],
[TL Name];
SQL SELECT * FROM....
ORDER BY [Update date] DESC;
SET DateFormat='D-MMM-YY';
Input:
Load * Inline
[
Update date, Talisma Name, Team Name, TL Name
31-Jan-16, Naresh, The Achievers, Krunal Panchal
1-Feb-16, Naresh, Silver Hawks, Sumeet Chothani
22-Apr-16, Naresh, The Victorious, Hemant Mohapatra
31-Jan-16, Naresh3, The Achievers, Krunal Panchal
1-Feb-16, Naresh3, Silver Hawks, Sumeet Chothani
22-Apr-16, Naresh3, The Victorious, Hemant Mohapatra
];
Left Join(Input)
Load [Talisma Name], Date(Min([Update date])) as MinDate Resident Input Group By [Talisma Name];
Output:
Load
Date([Update date]) as Start_date,
Date(If(MinDate = [Update date], MinDate, If([Talisma Name] = Previous([Talisma Name]), Previous([Update date])-1, Today()-1))) as End_date,
[Talisma Name],
[Team Name],
[TL Name]
Resident Input
Order By [Talisma Name], [Update date] Desc ;
Drop Table Input;
Dear Manish,
Thanks for your support ...Everything is ok ..but is not giving as expected output over single entry.
For Example
Update_date Talisma_Name TL_NAME
1-Mar-2016 Sandhya Hemant Mohamatra
Expected Output should be like belew
Start_date End_Date TalismaName TL_Name
1-Mar-2016 11-May-2016(Today()-1) Naresh KrunalPunchal
As of now as per the logic given by you output populating like belew
Start_date End_Date TalismaName TL_Name
1-Mar-2016 1-Mar-2016 Naresh KrunalPunchal
Kindly check or suggest me how to achieve the same !!
Thanks
Sarfaraz
Have you looked at my response above?
LOAD [Update date] AS Start_Date,
Date(Alt(Previous([Update date]), Today()) - 1) as End_Date,
[Talisma Name],
[Team Name],
[TL Name]
Resident Table
Order By [Update date] desc;
yes i have looked at your solution also and it is giving as expected output for the data which i have given in my post ..
However, for different talisma name and updates i am not getting as expected output ...It is getting crash
Raw_data is enclosed here for your reference from which i have to make report.
Thanks
Sarfaraz
Dear Jonathan,
Your solution is giving correct result for the data which i have given in my Post above.
However , I am not getting as expected result for bulk of data i.e in case of N numbers of Update dates and N number of Agent / Talisma Name
Raw_data is enclosed here for your reference from which i have to make report.
Thanks
Sarfaraz