Merging two dates fields into one

[sarfaraz_sheikh]

Dear All,

How i can build the logic for below SQL query in QlikView

Select A.LeadNo,RoleName, SalesCloseddate as LeadGeneratedDate

from STP_LMS_LEAD A inner join STP_LMS_ROLES

Union ALL

Select A.LeadNo,RoleName,LeadGeneratedDate as LeadGeneratedDate

from STP_LMS_LEAD A inner join STP_LMS_ROLES

group by LeadGeneratedDate,RoleName

Note in above query there is two different dates field ( Sales_Closed_Date ) and (LeadGeneratedDate) and both treated as LeadGeneratedDate ...can i build this logic in Qlikview

If yes then help with simplest solution ...and help would be much appreciated..

gwassenaar‌jagan‌ please comment on this need your help

Sarfaraz

[tresesco]

In QlikView you can try like:

Table:

Load A, B, Date1 as Date From <>;

Load A, B, Date2 as Date From <>;

With all fields common the two tables will get concatenated to produce a single table.

[jagan]

Hi,

Try like this

Data:

Select A.LeadNo,RoleName,Convert(DateTime,Convert(Varchar(10),SalesCloseddate,103),103) as LeadGeneratedDate,

'SalesClosed' AS DataType

from STP_LMS_LEAD(NOLOCK) A inner join STP_LMS_ROLES;

Concatenate(Data)

Select A.LeadNo,RoleName,Convert(DateTime,Convert(Varchar(10),LeadGeneratedDate,103),103) as LeadGeneratedDate,

'LeadGenerated' AS DataType

from STP_LMS_LEAD(NOLOCK) A inner join STP_LMS_ROLES

group by LeadGeneratedDate,RoleName;

Hope this helps you.

Regards,

Jagan.

[sarfaraz_sheikh]

Dear Jagan,

Is there any possibilities to do this on chart level as my table contains much more fields..

Sarfaraz

[jagan]

Hi,

Without seeing your data model and the data how can I suggest, attach some sample data and your expected results then it would be easier to understand and provide the solution.

Also, if you do this at script level it is very easier to deal in front end.

Regards,

Jagan.

[hic]

It sounds to me as if Sales_Closed_Date and LeadGeneratedDate are two very different things, and that they should not be merged into one field. But you can still solve the problem by creating a canonical date in the script.

See

Why You sometimes should Load a Master Table several times

Canonical Date

HIC

[sarfaraz_sheikh]

Dear Henric,

Can you keep any example here for my understanding related with canonical date based on below script;

Data:

LOAD

Status,

RoleName,

status_id,

LeadGeneratedDate,

LeadGeneratedDate  as Main_Date

Sales_Closed_date,

FROM

[\\10.62.211.141\hdfc_qlikview\DATA\RAW_QVD\IDST_LMS_LEAD.qvd]

(qvd);

concatenate (Data)

LOAD

Status,

RoleName,

status_id,

LeadGeneratedDate,

Sales_Closed_date as Main_Date,

Sales_Closed_date,

FROM

[\\10.62.211.141\hdfc_qlikview\DATA\RAW_QVD\IDST_LMS_LEAD.qvd]

(qvd);

Sarfaraz

[hic · Accepted solution]

Basically you are creating a canonical date in your Main_Date, but you duplicate data by loading the table twice. This will become a problem if you do a count or Sum on a field.

Instead you could do the following:

Data:

LOAD

   RecNo() as PrimaryKey,

   Status,

   RoleName,

   status_id,

   LeadGeneratedDate,

   Sales_Closed_date

   FROM ...

Bridge:

Load PrimaryKey, LeadGeneratedDate as Date, 'Generated' as Type Resident Data;

Load PrimaryKey, Sales_Closed_date as Date, 'Closed' as Type Resident Data;

HIC