Solved: Help me to derive some logic to build the one repo... - Qlik Community

govind1501 · ‎2020-05-07

Hi All,

I want to display distinct combination (customer + country+branch) of records in my report table. Please find my table and expected output. Please help me to derive logic.

- Display the all the distinct records (key group of customer+country+branch) irrespective of timestamp.

- Display the maximum date record if the key group is same on particular day.

- Customer can repeat on different days and we can display in report.

- TOTAL values need to be added on that particular day, if the INPUT column value and OUTPUT column value is different on the maximum date record .

- TOTAL value need to be displayed on the TOTAL cell as it is, If the INPUT value and OUTPUT value is same.

I have covered all scenarios in below tables. both main table and expected output. please help me with this complex requirement.

My Table:

CUSTOMER	COUNTRY	BRANCH	INPUT	OUTPUT	TOTAL	DATE
276	GB		57	29	1	9/13/2019 10:00:58 AM
276	GB	GB-HR	57	29	1	9/13/2019 10:31:23 AM
276	GB	GB-HR	57	29	1	9/13/2019 10:51:23 AM
276	GB	GB-HR	57	27	1	9/16/2019 10:46:13 AM
276	GB	GB-HS	12	29	1	9/16/2019 10:50:48 AM
276	GB	GB-HR	57	29	5	9/30/2019 10:11:58 AM
276	GB	GB-HR	57	15	3	9/30/2019 10:20:58 AM
9001	IN	IN-HS	5	12	1	10/30/2019 8:00:18 AM
9001	IN	IN-HS	11	12	2	10/30/2019 9:00:18 AM
9001	IN	IN-HS	11	14	2	10/30/2019 9:30:18 AM
9001	IN	IN-HS	10	12	3	10/30/2019 10:00:18 AM
9001	IN	IN-HS	11	14	2	11/30/2019 11:30:18 AM
9001	IN	NZ-BB	57	30	5	10/30/2019 10:20:58 AM
9001	IN	NZ-BB	57	57	5	10/30/2019 11:30:48 AM

Expected Output:

CUSTOMER	COUNTRY	BRANCH	INPUT	OUTPUT	TOTAL	DATE
276	GB		57	29	1	9/13/2019 10:00:58 AM
276	GB	GB-HR	57	29	1	9/13/2019 10:51:23 AM
276	GB	GB-HR	57	27	1	9/16/2019 10:46:13 AM
276	GB	GB-HS	12	29	1	9/16/2019 10:50:48 AM
276	GB	GB-HR	57	15	8	9/30/2019 10:20:58 AM
9001	IN	IN-HS	10	12	8	10/30/2019 10:00:18 AM
9001	IN	IN-HS	11	14	2	11/30/2019 11:30:18 AM
9001	IN	NZ-BB	57	57	10	10/30/2019 11:30:48 AM

Thanks in advance.

Saravanan_Desingh · ‎2020-05-08

Please check.

tab1:
LOAD *, Date(Floor(DATE)) As tmpDt INLINE [
    CUSTOMER, COUNTRY, BRANCH, INPUT, OUTPUT, TOTAL, DATE
    276, GB,  , 57, 29, 1, 9/13/2019 10:00:58 AM
    276, GB, GB-HR, 57, 29, 1, 9/13/2019 10:31:23 AM
    276, GB, GB-HR, 57, 29, 1, 9/13/2019 10:51:23 AM
    276, GB, GB-HR, 57, 27, 1, 9/16/2019 10:46:13 AM
    276, GB, GB-HS, 12, 29, 1, 9/16/2019 10:50:48 AM
    276, GB, GB-HR, 57, 29, 5, 9/30/2019 10:11:58 AM
    276, GB, GB-HR, 57, 15, 3, 9/30/2019 10:20:58 AM
    9001, IN, IN-HS, 5, 12, 1, 10/30/2019 8:00:18 AM
    9001, IN, IN-HS, 11, 12, 2, 10/30/2019 9:00:18 AM
    9001, IN, IN-HS, 11, 14, 2, 10/30/2019 9:30:18 AM
    9001, IN, IN-HS, 10, 12, 3, 10/30/2019 10:00:18 AM
    9001, IN, IN-HS, 11, 14, 2, 11/30/2019 11:30:18 AM
    9001, IN, NZ-BB, 57, 30, 5, 10/30/2019 10:20:58 AM
    9001, IN, NZ-BB, 57, 57, 5, 10/30/2019 11:30:48 AM
];

Right Join(tab1)
LOAD CUSTOMER, COUNTRY, BRANCH,  tmpDt, Timestamp(Max(DATE)) As DATE, 		If((Count(DISTINCT INPUT)=1 And Count(DISTINCT OUTPUT)=1) Or Index(Concat(DISTINCT INPUT=OUTPUT,','),'-1')>0, Only(TOTAL), Sum(TOTAL)) As NewTOTAL
Resident tab1
Group By CUSTOMER, COUNTRY, BRANCH, tmpDt
;

Drop Field tmpDt;

View solution in original post

Saravanan_Desingh · ‎2020-05-07

Can u check this?

tab1:
LOAD *, Date(Floor(DATE)) As tmpDt INLINE [
    CUSTOMER, COUNTRY, BRANCH, INPUT, OUTPUT, TOTAL, DATE
    276, GB,  , 57, 29, 1, 9/13/2019 10:00:58 AM
    276, GB, GB-HR, 57, 29, 1, 9/13/2019 10:31:23 AM
    276, GB, GB-HR, 57, 29, 1, 9/13/2019 10:51:23 AM
    276, GB, GB-HR, 57, 27, 1, 9/16/2019 10:46:13 AM
    276, GB, GB-HS, 12, 29, 1, 9/16/2019 10:50:48 AM
    276, GB, GB-HR, 57, 29, 5, 9/30/2019 10:11:58 AM
    276, GB, GB-HR, 57, 15, 3, 9/30/2019 10:20:58 AM
    9001, IN, IN-HS, 5, 12, 1, 10/30/2019 8:00:18 AM
    9001, IN, IN-HS, 11, 12, 2, 10/30/2019 9:00:18 AM
    9001, IN, IN-HS, 11, 14, 2, 10/30/2019 9:30:18 AM
    9001, IN, IN-HS, 10, 12, 3, 10/30/2019 10:00:18 AM
    9001, IN, IN-HS, 11, 14, 2, 11/30/2019 11:30:18 AM
    9001, IN, NZ-BB, 57, 30, 5, 10/30/2019 10:20:58 AM
    9001, IN, NZ-BB, 57, 57, 5, 10/30/2019 11:30:48 AM
];

Right Join(tab1)
LOAD CUSTOMER, COUNTRY, BRANCH,  tmpDt, Timestamp(Max(DATE)) As DATE
Resident tab1
Group By CUSTOMER, COUNTRY, BRANCH, tmpDt//, INPUT, OUTPUT, TOTAL
;

Drop Field tmpDt;

govind1501 · ‎2020-05-07

Thanks for response saran. One scenario is not covered, ie TOTAL. Whenever INPUT AND OUTPUT value is same then we can display TOTAL as it is, but whenever INPUT AND OUTPUT value is different, then need to add the TOTAL values in that particular day. Please find the expected output to cover these scenarios.

Here total added of records on that particular day if INPUT AND OUTPUT is different in main table (ex: first two records TOTAL)

Second record TOTAL displayed as it is as INPUT AND OUTPUT is same.

CUSTOMER	COUNTRY	BRANCH	INPUT	OUTPUT	TOTAL	DATE
276	GB	GB-HR	57	15	8	09/30/2019 10:20:58
9001	IN	IN-HS	10	12	8	10/30/2019 10:00:18
9001	IN	NZ-BB	57	57	5	10/30/2019 11:30:48

Saravanan_Desingh · ‎2020-05-07

Please check.

tab1:
LOAD *, Date(Floor(DATE)) As tmpDt INLINE [
    CUSTOMER, COUNTRY, BRANCH, INPUT, OUTPUT, TOTAL, DATE
    276, GB,  , 57, 29, 1, 9/13/2019 10:00:58 AM
    276, GB, GB-HR, 57, 29, 1, 9/13/2019 10:31:23 AM
    276, GB, GB-HR, 57, 29, 1, 9/13/2019 10:51:23 AM
    276, GB, GB-HR, 57, 27, 1, 9/16/2019 10:46:13 AM
    276, GB, GB-HS, 12, 29, 1, 9/16/2019 10:50:48 AM
    276, GB, GB-HR, 57, 29, 5, 9/30/2019 10:11:58 AM
    276, GB, GB-HR, 57, 15, 3, 9/30/2019 10:20:58 AM
    9001, IN, IN-HS, 5, 12, 1, 10/30/2019 8:00:18 AM
    9001, IN, IN-HS, 11, 12, 2, 10/30/2019 9:00:18 AM
    9001, IN, IN-HS, 11, 14, 2, 10/30/2019 9:30:18 AM
    9001, IN, IN-HS, 10, 12, 3, 10/30/2019 10:00:18 AM
    9001, IN, IN-HS, 11, 14, 2, 11/30/2019 11:30:18 AM
    9001, IN, NZ-BB, 57, 30, 5, 10/30/2019 10:20:58 AM
    9001, IN, NZ-BB, 57, 57, 5, 10/30/2019 11:30:48 AM
];

Right Join(tab1)
LOAD CUSTOMER, COUNTRY, BRANCH,  tmpDt, Timestamp(Max(DATE)) As DATE, 		If(Not(Count(DISTINCT INPUT)=1 And Count(DISTINCT OUTPUT)=1), Sum(TOTAL), Only(TOTAL)) As NewTOTAL
Resident tab1
Group By CUSTOMER, COUNTRY, BRANCH, tmpDt
;

Drop Field tmpDt;

govind1501 · ‎2020-05-07

Saran - You are there to output. Perfect. one thing missed. Please advise

No need to add only if INPUT and OUTPUT is same. In below case no need to add TOTAL as INPUT and OUTPUT is same.

Main Table Records:

CUSTOMER	COUNTRY	BRANCH	INPUT	OUTPUT	TOTAL	DATE
9001	IN	NZ-BB	57	30	5	10/30/2019 10:20:58
9001	IN	NZ-BB	57	57	5	10/30/2019 11:30:48

Expected Output: Total not added here. In our response it is showing 10.

CUSTOMER	COUNTRY	BRANCH	INPUT	OUTPUT	TOTAL	DATE
9001	IN	NZ-BB	57	57	5	10/30/2019 11:30:48

Saravanan_Desingh · ‎2020-05-08

Please check.

tab1:
LOAD *, Date(Floor(DATE)) As tmpDt INLINE [
    CUSTOMER, COUNTRY, BRANCH, INPUT, OUTPUT, TOTAL, DATE
    276, GB,  , 57, 29, 1, 9/13/2019 10:00:58 AM
    276, GB, GB-HR, 57, 29, 1, 9/13/2019 10:31:23 AM
    276, GB, GB-HR, 57, 29, 1, 9/13/2019 10:51:23 AM
    276, GB, GB-HR, 57, 27, 1, 9/16/2019 10:46:13 AM
    276, GB, GB-HS, 12, 29, 1, 9/16/2019 10:50:48 AM
    276, GB, GB-HR, 57, 29, 5, 9/30/2019 10:11:58 AM
    276, GB, GB-HR, 57, 15, 3, 9/30/2019 10:20:58 AM
    9001, IN, IN-HS, 5, 12, 1, 10/30/2019 8:00:18 AM
    9001, IN, IN-HS, 11, 12, 2, 10/30/2019 9:00:18 AM
    9001, IN, IN-HS, 11, 14, 2, 10/30/2019 9:30:18 AM
    9001, IN, IN-HS, 10, 12, 3, 10/30/2019 10:00:18 AM
    9001, IN, IN-HS, 11, 14, 2, 11/30/2019 11:30:18 AM
    9001, IN, NZ-BB, 57, 30, 5, 10/30/2019 10:20:58 AM
    9001, IN, NZ-BB, 57, 57, 5, 10/30/2019 11:30:48 AM
];

Right Join(tab1)
LOAD CUSTOMER, COUNTRY, BRANCH,  tmpDt, Timestamp(Max(DATE)) As DATE, 		If((Count(DISTINCT INPUT)=1 And Count(DISTINCT OUTPUT)=1) Or Index(Concat(DISTINCT INPUT=OUTPUT,','),'-1')>0, Only(TOTAL), Sum(TOTAL)) As NewTOTAL
Resident tab1
Group By CUSTOMER, COUNTRY, BRANCH, tmpDt
;

Drop Field tmpDt;

Saravanan_Desingh · ‎2020-05-09

Check this.

tab1:
LOAD *, Date(Floor(DATE)) As tmpDt INLINE [
    CUSTOMER, COUNTRY, BRANCH, INPUT, OUTPUT, TOTAL, DATE
    276, GB,  , 57, 29, 1, 9/13/2019 10:00:58 AM
    276, GB, GB-HR, 57, 29, 1, 9/13/2019 10:31:23 AM
    276, GB, GB-HR, 57, 29, 1, 9/13/2019 10:51:23 AM
    276, GB, GB-HR, 57, 27, 1, 9/16/2019 10:46:13 AM
    276, GB, GB-HS, 12, 29, 1, 9/16/2019 10:50:48 AM
    276, GB, GB-HR, 57, 29, 5, 9/30/2019 10:11:58 AM
    276, GB, GB-HR, 57, 15, 3, 9/30/2019 10:20:58 AM
    9001, IN, IN-HS, 5, 12, 1, 10/30/2019 8:00:18 AM
    9001, IN, IN-HS, 11, 12, 2, 10/30/2019 9:00:18 AM
    9001, IN, IN-HS, 11, 14, 2, 10/30/2019 9:30:18 AM
    9001, IN, IN-HS, 10, 12, 3, 10/30/2019 10:00:18 AM
    9001, IN, IN-HS, 11, 14, 2, 11/30/2019 11:30:18 AM
    9001, IN, NZ-BB, 57, 30, 5, 10/30/2019 10:20:58 AM
    9001, IN, NZ-BB, 57, 57, 5, 10/30/2019 11:30:48 AM
];

Right Join(tab1)
LOAD CUSTOMER, COUNTRY, BRANCH,  tmpDt, Min(INPUT) As Min_INPUT, Max(INPUT) As Max_INPUT
Resident tab1
Group By CUSTOMER, COUNTRY, BRANCH, tmpDt
;

tab2:
NoConcatenate
LOAD * Resident tab1;
Right Join(tab2)
LOAD CUSTOMER, COUNTRY, BRANCH,  tmpDt, Timestamp(Max(DATE)) As DATE, Count(DISTINCT INPUT) As iCnt,
		Count(DISTINCT OUTPUT) As oCnt, Index(Concat(DISTINCT INPUT=OUTPUT,','),'-1')>0 As ioCat,
		If((Count(DISTINCT INPUT)=1 And Count(DISTINCT OUTPUT)=1) Or Index(Concat(DISTINCT INPUT=OUTPUT,','),'-1')>0, Only(TOTAL), Sum([TOTAL])) As NewTOTAL,
		If(Concat(DISTINCT Min_INPUT=Max_INPUT,',')='-1', 'Y') As INPUT_Flag
Resident tab1
Group By CUSTOMER, COUNTRY, BRANCH, tmpDt
;

tab3:
NoConcatenate
LOAD *, If(INPUT_Flag='Y',INPUT, Min_INPUT&':'&INPUT) As NewINPUT
Resident tab2
;

Drop Field tmpDt;
DROP Table tab1, tab2;

govind1501 · ‎2020-05-09

Just found one bug on below case. We need to display the min value based on the timestamp on particular day. (here 5 correct) but if we change value from 5 to 30 in inline load then it should show 30:10 but in our case, we are getting 10:10. In the same way max value also. As we already getting the max records on particular that is fine. Min value need to corrected.

CUSTOMER	COUNTRY	BRANCH	OUTPUT	DATE	INPUT	Min_INPUT	Max_INPUT	NewINPUT	NewTOTAL
276	GB		29	9/13/2019 10:00:58 AM	57	57	57	57	1
276	GB	GB-HR	15	9/30/2019 10:20:58 AM	57	57	57	57	8
276	GB	GB-HR	27	9/16/2019 10:46:13 AM	57	57	57	57	1
276	GB	GB-HR	29	9/13/2019 10:51:23 AM	57	57	57	57	1
276	GB	GB-HS	29	9/16/2019 10:50:48 AM	12	12	12	12	1
9001	IN	IN-HS	12	10/30/2019 10:00:18 AM	10	10	30	10:10	8
9001	IN	IN-HS	14	11/30/2019 11:30:18 AM	11	11	11	11	2
9001	IN	NZ-BB	57	10/30/2019 11:30:48 AM	57	57	57	57	5

Help me to derive some logic to build the one report.

App Development

complex requirement

QlikView Development