Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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

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

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

vsudhakar
Creator III
Creator III

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