Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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';
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
Is it a cross join
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
ID | Category | Product | Sales |
100 | A | A1 | 130 |
100 | A | A2 | 125 |
101 | B | B1 | 210 |
Hope it helps...
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;