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: 
achakilam1022
Creator II
Creator II

Mapping date and duration in Call Log Data(QlikSense)

Hi all,

I am working on a small project that analyzes call data.

I've issues mapping the call duration from the call log data to the price table to obtain the revenue earned (Revenue column in CallData table) .

A unique phone# is assigned to each company. If a call is made using phone#1 ( for company A) on any date from 1/2/2017 and before 1/5/2017 and duration is >= 20 sec then the revenue should be 5$. From 1/5/2017 on wards the call duration has to be >= 30 sec to earn 5$ till 2/2/2017 where again the call duration has been updated. Please help me in writing a script to generate the Revenue column.

PriceData.PNGCallData.PNG

Please find the attached data files.

I appreciate any help with this.

Thanks,

Amuktha

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Yes.

To avoid You need to Left Join Table and drop Table Price (if You don't need)

[Call Data]:
LOAD [Call ID],
Company,
Phone#,
CallDate as Date,
PhoneDuration
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1330130-292796/CallDataSample.xlsx"
(ooxml, embedded labels, table is Sheet1);
Temp:
LOAD Company,
Phone#,
[Date(MM/DD/YYYY)] as Date,
[Duration(sec)] as Duration,
[Price($)] as Price
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1330130-292795/PriceDataSample.xlsx"
(ooxml, embedded labels, table is Sheet1);
Join LOAD Company,Phone#,Duration,Price,Date,
If(Company=Peek(Company),Peek(Date),Date(ToDay())) as DateEnd
Resident Temp Order By Company,Date desc;
Price:
NoConcatenate LOAD Company,Phone#,Duration,Price,
Date(Date+IterNo()-1) as Date
Resident Temp
While Date(Date+IterNo()-1) < DateEnd;
Drop Table Temp;
Left Join ([Call Data])
LOAD Company,Date,Duration,Price
Resident Price;
Drop Table
Price;

View solution in original post

4 Replies
antoniotiman
Master III
Master III

Hi Amuktha,

try this

[Call Data]:
LOAD [Call ID],
Company,
// Phone#,
   CallDate as Date,
PhoneDuration
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1330130-292796/CallDataSample.xlsx"
(ooxml, embedded labels, table is Sheet1);
Temp:
LOAD Company,
Phone#,
[Date(MM/DD/YYYY)] as Date,
[Duration(sec)] as Duration,
[Price($)] as Price
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1330130-292795/PriceDataSample.xlsx"
(ooxml, embedded labels, table is Sheet1);
Join LOAD Company,Phone#,Duration,Price,Date,
If(Company=Peek(Company),Peek(Date),Date(ToDay())) as DateEnd
Resident Temp Order By Company,Date desc;
Price:
NoConcatenate LOAD Company,Phone#,Duration,Price,
Date(Date+IterNo()-1) as Date
Resident Temp
While Date(Date+IterNo()-1) < DateEnd;
Drop Table
Temp;

Expression Revenue :

If(IsNull(Price),'N/A',If(PhoneDuration >= Duration,Price,0))

Regards,

Antonio

achakilam1022
Creator II
Creator II
Author

Hi Antonio,

Thanks a lot for your answer. I've tried this and it generated a synthetic key combining Company and Date.

Thanks again.

-Amuktha

antoniotiman
Master III
Master III

Yes.

To avoid You need to Left Join Table and drop Table Price (if You don't need)

[Call Data]:
LOAD [Call ID],
Company,
Phone#,
CallDate as Date,
PhoneDuration
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1330130-292796/CallDataSample.xlsx"
(ooxml, embedded labels, table is Sheet1);
Temp:
LOAD Company,
Phone#,
[Date(MM/DD/YYYY)] as Date,
[Duration(sec)] as Duration,
[Price($)] as Price
FROM
"https://community.qlik.com/servlet/JiveServlet/download/1330130-292795/PriceDataSample.xlsx"
(ooxml, embedded labels, table is Sheet1);
Join LOAD Company,Phone#,Duration,Price,Date,
If(Company=Peek(Company),Peek(Date),Date(ToDay())) as DateEnd
Resident Temp Order By Company,Date desc;
Price:
NoConcatenate LOAD Company,Phone#,Duration,Price,
Date(Date+IterNo()-1) as Date
Resident Temp
While Date(Date+IterNo()-1) < DateEnd;
Drop Table Temp;
Left Join ([Call Data])
LOAD Company,Date,Duration,Price
Resident Price;
Drop Table
Price;

achakilam1022
Creator II
Creator II
Author

Wow! This is perfect.

Thanks a lot, Antonio.