Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sarfaraz_sheikh
Contributor III
Contributor III

Making two date from one date based on assumption

Dear ALL,

I have the following data and I needs to calculate Start_Date and End_Date from Update date.

Input:  

Update dateTalisma NameTeam NameTL Name
31-Jan-16  Naresh   MedicalaThe AchieversKrunal Panchal
1-Feb-16Naresh MedicalaSilver HawksSumeet Chothani
22-Apr-16Naresh MedicalaThe VictoriousHemant 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

11 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunny_talwar

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;


Capture.PNG

jonathandienst
Partner - Champion III
Partner - Champion III

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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MK_QSL
MVP
MVP

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;

sarfaraz_sheikh
Contributor III
Contributor III
Author

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

sunny_talwar

Have you looked at my response above?

Re: Making two date from one date based on assumption

Anonymous
Not applicable

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;

sarfaraz_sheikh
Contributor III
Contributor III
Author

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

sarfaraz_sheikh
Contributor III
Contributor III
Author

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