topic Re: Concatenation not happening in QlikView

Concatenation not happening

stevejones1 — Fri, 18 Jan 2019 23:12:55 GMT

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;

Re: Concatenation not happening

shiveshsingh — Sat, 19 Jan 2019 05:37:42 GMT

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);

Re: Concatenation not happening

stevejones1 — Mon, 21 Jan 2019 02:23:19 GMT

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

Re: Concatenation not happening

stevejones1 — Tue, 22 Jan 2019 05:07:29 GMT

Is it a cross join

Re: Concatenation not happening

Nagaraju_KCS — Mon, 25 Mar 2019 10:01:46 GMT

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...

Re: Concatenation not happening

Nagaraju_KCS — Mon, 25 Mar 2019 10:22:30 GMT

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;