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: 
stevejones1
Contributor III
Contributor III

Concatenation not happening

Hi 

Below is my script , i cannot see all the Type values for Type column except the first one(ActiveCustomerService180) what am i doing wrong

 

Temp:
LOAD Date("QtrStartDate",'MM/DD/YYYY') as [QtrStart],
Date(QtrEndDate,'MM/DD/YYYY') as CommonDate,
PeriodDate
FROM Calendar.qvd(qvd);

Inner Join(Temp)
LOAD
'ActiveCustomerService180' AS Type,
ORGANIZATIONID,
DATE(NewCustomerDate,'MM/DD/YYYY') AS CusStartDate,
DATE(ServiceExpirationDate180,'MM/DD/YYYY') AS CusEndDate
RESIDENT LatteData
WHERE DATE(ServiceExpirationDate180,'MM/DD/YYYY') >= '02/04/2017';

CONCATENATE(Temp)

LOAD
'ActiveCustomerLastPing180' AS Type,
ORGANIZATIONID,
DATE(NewCustomerDate,'MM/DD/YYYY') AS CusStartDate,
DATE(LastPingDate180,'MM/DD/YYYY') AS CusEndDate
RESIDENT LatteData
WHERE DATE(LastPingDate180,'MM/DD/YYYY') >= '02/04/2017';

CONCATENATE(Temp)

LOAD
'ActiveCustomer180' AS Type,
ORGANIZATIONID,
DATE(NewCustomerDate,'MM/DD/YYYY') AS CusStartDate,
DATE(CustomerChurnDate180,'MM/DD/YYYY') AS CusEndDate
RESIDENT LatteData
WHERE DATE(CustomerChurnDate180,'MM/DD/YYYY') >= '02/04/2017';

NoConcatenate

Customer:
Load * Resident Temp Where
CusStartDate <= CommonDate AND CusEndDate >= [QtrStart];

Drop Table Temp;

5 Replies
shiveshsingh
Master
Master

Hi

Can you check the output of below code?

on what basis you are performing Inner Join in both the tables?

 

Temp:
LOAD Date("QtrStartDate",'MM/DD/YYYY') as [QtrStart],
Date(QtrEndDate,'MM/DD/YYYY') as CommonDate,
PeriodDate
FROM Calendar.qvd(qvd);

Inner Join(Temp)
LOAD
'ActiveCustomerService180' AS Type,
ORGANIZATIONID,
DATE(NewCustomerDate,'MM/DD/YYYY') AS CusStartDate,
DATE(ServiceExpirationDate180,'MM/DD/YYYY') AS CusEndDate
RESIDENT LatteData
WHERE DATE(ServiceExpirationDate180,'MM/DD/YYYY') >= '02/04/2017';

stevejones1
Contributor III
Contributor III
Author

Even i am not sure , i am following the help provided here 

 

https://community.qlik.com/t5/New-to-Qlik-Community/Active-Members-Count/m-p/1531298#M8284

 

 

stevejones1
Contributor III
Contributor III
Author

Is it a cross join

qv_testing
Specialist II
Specialist II

Hi Steve,

Inner Join: Join will combine the rows where the key values matches, (It should be one mapping field between 2 tables, otherwise it ll take cross join)

Example
Category
Load * inline [
ID, Category
100, A
101, B
102, C
];

Inner Join(Category)

Product
LOAD * inline [
ID, Product, Sales
100, A1, 130
100, A2, 125
101, B1, 210
];

 

Output

IDCategoryProductSales
100AA1130
100AA2125
101BB1210

 

Hope it helps...

qv_testing
Specialist II
Specialist II

About this link, 

you can refer his solutions  Jonathan solution.

ActiveQtr: 
LOAD * INLINE [
    CommonDate,RecCount
    02/01/2017,1
    08/01/2017,1
    11/01/2017,1
    02/01/2018,1
    05/01/2018,1
    08/01/2018,1
    11/01/2018,1
    02/01/2019,1
];

Left Join (ActiveQtr)
IntervalMatch (CommonDate)
LOAD NewCustomerDate, ServiceExpirationDate180 
RESIDENT LatteData 
WHERE ServiceExpirationDate180 >= MakeDate(2018,1,2);

Left Join (ActiveQtr)
LOAD *
RESIDENT LatteData; 

Drop Table LatteData;