topic Re: Making two date from one date based on assumption in QlikView

Making two date from one date based on assumption

sarfaraz_sheikh — Thu, 12 May 2016 08:56:09 GMT

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

Re: Making two date from one date based on assumption

jonathandienst — Thu, 12 May 2016 09:02:30 GMT

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;

Re: Making two date from one date based on assumption

sunny_talwar — Thu, 12 May 2016 09:02:41 GMT

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;

Re: Making two date from one date based on assumption

jonathandienst — Thu, 12 May 2016 09:04:13 GMT

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;

Re: Making two date from one date based on assumption

MK_QSL — Thu, 12 May 2016 09:17:34 GMT

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;

Re: Making two date from one date based on assumption

sarfaraz_sheikh — Thu, 12 May 2016 12:36:12 GMT

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

Re: Making two date from one date based on assumption

sunny_talwar — Thu, 12 May 2016 12:37:19 GMT

Have you looked at my response above?

Re: Making two date from one date based on assumption

Anonymous — Thu, 12 May 2016 12:47:32 GMT

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;

Re: Making two date from one date based on assumption

sarfaraz_sheikh — Thu, 12 May 2016 13:09:37 GMT

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

Re: Making two date from one date based on assumption

sarfaraz_sheikh — Fri, 13 May 2016 06:54:39 GMT

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

Re: Making two date from one date based on assumption

jonathandienst — Fri, 13 May 2016 07:22:15 GMT

I can't open rar files from where I am currently working. Could you upload a zip?

Re: Making two date from one date based on assumption

sarfaraz_sheikh — Fri, 13 May 2016 07:32:43 GMT

I don't have winzip installed in my machine ....for your reference i am attaching XL file.

Thanks

Sarfaraz