Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

achakilam1022
Contributor

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

Tags (1)
1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Mapping date and duration in Call Log Data(QlikSense)

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;

4 Replies
antoniotiman
Honored Contributor III

Re: Mapping date and duration in Call Log Data(QlikSense)

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
Contributor

Re: Mapping date and duration in Call Log Data(QlikSense)

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
Honored Contributor III

Re: Mapping date and duration in Call Log Data(QlikSense)

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
Contributor

Re: Mapping date and duration in Call Log Data(QlikSense)

Wow! This is perfect.

Thanks a lot, Antonio.