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: 
bnelson111
Creator
Creator

Joined a table but no time stamp in joined table.

Good morning, i have a interesting question in relation to joining a two table together with a common data field in this case its the kjobcode. Everything works okay however i don't have a Month Completed or Year Completed from the second table see below, i have provide a line from each original table. First has a end date which i then make a Month and Year for analysis. Second line is the joined table which has no date? How do i take the date from the 1st table?

Day CompleteddescriptionEnd Dateevent-qtyevent-typekjobcodemach-idMonth CompletedYear Completed
15Running15/03/20192092G2117793PRT-NOVMar2019
 Print LAS 13903 2117793PRT  

 

Also below is the Load Script, i hope this makes sense? Thanks in advance. i have also included a pivot table result.

Pivot GraphPivot Graph


[SFDC DATA]:


LOAD

 

kjobcode,

"mach-id",
"stat-code",
"op-code",
"start-time",
Interval#( "elapse-time",'s') as "Seconds",

"elapse-time",

"event-type",

date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YYYY') as "End Date",
day(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Day Completed",
Month(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Month Completed",
year(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YY')) AS "Year Completed",
date(Date(Date('1990-01-01 00:00:00.000') +Num#("start-time"/86400)),'DD/MM/YYYY') as "Event Logged Time",
If(Frac(date(Date(Date('1990-01-01 00:00:00.000') +Num#("end-time"/86400)),'DD/MM/YYYY')) >= MakeTime(8) and Frac(date(Date(Date('1990-01-01 00:00:00.000')+Num#("end-time"/86400)),'DD/MM/YYYY')) <= MakeTime(20), 'Day 08:00 TO 20:00', 'Night 20:00 TO 08:00') as B,

amended,

"group-code",
"seq-num",
"sub-type",

"event-qty";

SQL SELECT

kjobcode,
"end-time",

"mach-id",
"stat-code",
"op-code",
"start-time",
"elapse-time",

"event-type",

amended,

"group-code",
"seq-num",
"sub-type",

"event-qty"

FROM PUB.sfeventcds

WHERE "start-time" > 915270818;

// $(varMaxDate)use 789044901 757500265 $(varMaxDate) was 851346857, used > 865121989, < 882744802

 

left join
LOAD "stat-code",
description,
"type",
cost,
kco,
movement,
indirect,
"trans-cost",
"sundry-code",
"restrict-flag",
"sub-type",
PlantCode;
SQL SELECT *
FROM PUB.sfstatcode;
//-------- End Multiple Select Statements ------

ODBC CONNECT32 TO visionu (XUserId is aSPAHaRP, XPassword is aKRRBaNV);


join

[LAS]:

LOAD JobCode as kjobcode,

if(StepNum='1150', 'Print LAS' , if (StepNum='1250','Slit LAS' )) as description,
if(StepNum='1150', '3PRT',if(StepNum='1250', '4SL' ) ) as "mach-id",

UnrecScrapQty as [event-qty];

SQL SELECT
JobCode,
StepNum,
UnrecScrapQty

FROM PUB."PV_JobStepVar"
Where UnrecScrapQty > 1;

 

 

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

Try this

[LAS]:
LOAD JobCode as kjobcode,
     if(StepNum='1150', 'Print LAS' , if (StepNum='1250','Slit LAS' )) as description,
     if(StepNum='1150', '3PRT',if(StepNum='1250', '4SL' ) ) as [mach-id],
     RunNumber,
     UnrecScrapQty as [event-qty];
SQL SELECT
    JobCode,
    StepNum,
    RunNumber,
    UnrecScrapQty
FROM PUB."PV_JobStepVar"
WHERE UnrecScrapQty  > 0;

Right Join (LAS)
LOAD kjobcode,
     Max(RunNumber) as RunNumber
Resident LAS
Group By kjobcode;

View solution in original post

27 Replies
asinha1991
Creator III
Creator III

if I understand your problem correctly , you want date to be populated based on join with kjobcode?

The problem is your join seems to be on 4 different field(as I understand from table)

description,event-qty,kjobcode,mach-id

 

so unless all field have same value, it will not populate. A way I can think of is Load kjobcode and extracted date separately and join with existing joined table(remove extracts from first load) or let it be separate(they will still associate)

I didn't go in detail at load 

 

bnelson111
Creator
Creator
Author

The only one that is joined is the Kjobcode.
asinha1991
Creator III
Creator III

can you show me the data model (ctrl+T)
bnelson111
Creator
Creator
Author

today.png

asinha1991
Creator III
Creator III

[LAS]:
LOAD JobCode as kjobcode,
if(StepNum='1150', 'Print LAS' , if (StepNum='1250','Slit LAS' )) as description1,
if(StepNum='1150', '3PRT',if(StepNum='1250', '4SL' ) ) as "mach-id1",

UnrecScrapQty as [event-qty1];


change LAS part and let me know the result, I have renamed all fields other than kjob, since this fields are already there in above loads join is created on multiple fields
bnelson111
Creator
Creator
Author

Doubling lines up.

 

Day Completeddescriptiondescription1elapse-timeevent-qty1mach-id1End Date
15Web BreakPrint LAS293139033PRT15/03/2019
15Web BreakSlit LAS2932717.4714SL15/03/2019
bnelson111
Creator
Creator
Author

Sorry should have included mach-id

 

Day Completeddescriptiondescription1elapse-timeevent-qty1mach-id1End Datemach-id
15Web BreakPrint LAS293139033PRT15/03/20194SL-TTNS
15Web BreakSlit LAS2932717.4714SL15/03/20194SL-TTNS
bnelson111
Creator
Creator
Author

Okay i have changed the approach on this one. I now have a table output as below.

today.png

The question is how do i either via load script or expression only show the max run number which maybe different for each kjobcode. So in above case max Run number is 76 and the only line showing should be line 2 for this Kjobcode. It must be by kjobcode.

 

Script Below.

[LAS]:

 


LOAD JobCode as kjobcode,

if(StepNum='1150', 'Print LAS' , if (StepNum='1250','Slit LAS' )) as description,
if(StepNum='1150', '3PRT',if(StepNum='1250', '4SL' ) ) as "mach-id",
RunNumber,
UnrecScrapQty as [event-qty];

SQL SELECT
JobCode,
StepNum,
RunNumber,
UnrecScrapQty

FROM PUB."PV_JobStepVar"
Where UnrecScrapQty > 1;

sunny_talwar


@bnelson111 wrote:

The question is how do i either via load script or expression only show the max run number which maybe different for each kjobcode. So in above case max Run number is 76 and the only line showing should be line 2 for this Kjobcode. It must be by kjobcode.


Front end is fairly simple. Create a chart with kjobcode as dimension and use Max(RunNumber) as your expression