Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
gwassenaarjagan please comment on this need your help
Sarfaraz
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
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.
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.
Dear Jagan,
Is there any possibilities to do this on chart level as my table contains much more fields..
Sarfaraz
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.
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
HIC
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
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