New to Qlik Sense

[achakilam1022]

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.

Please find the attached data files.

I appreciate any help with this.

Thanks,

Amuktha

[antoniotiman]

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]

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 · Accepted solution]

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]

Wow! This is perfect.

Thanks a lot, Antonio.