Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
bnelson111
Contributor

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.

today.pngPivot 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;

 

 

1 Solution

Accepted Solutions

Re: Joined a table but no time stamp in joined table.

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;
27 Replies
asinha1991
Contributor

Re: Joined a table but no time stamp in joined table.

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
Contributor

Re: Joined a table but no time stamp in joined table.

The only one that is joined is the Kjobcode.
asinha1991
Contributor

Re: Joined a table but no time stamp in joined table.

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

Re: Joined a table but no time stamp in joined table.

today.png

asinha1991
Contributor

Re: Joined a table but no time stamp in joined table.

[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
Contributor

Re: Joined a table but no time stamp in joined table.

Doubling lines up.

 

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

Re: Joined a table but no time stamp in joined table.

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
Contributor

Re: Joined a table but no time stamp in joined table.

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;

Re: Joined a table but no time stamp in joined table.


@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