I have to variables to know ST_Algemeen and ST_Algemeen1.
I want them to put together as 1 variable Common_ST.
Afterwards I can work with this common variable.
I have try out everything but without success.
For you I know very easy???
See the bold variables
FOR EACH vFile in FileList('lib://Transportlijsten\*.csv')
LET vQVD = replace(vFile, '.csv', '.qvd');
IF ALT(FileSize('$(vQVD)'), 0) = 0 then
NextFile:
Load*,
if(#ZnaarC=' 1 ' ,Namech,ST_Algemeen1) AS ST_common;
LOAD*,
Minute([Transporttijd])&':'& Second([Transporttijd]) As Transporttijd2;
LOAD*,
[Endtime_common] -[Starttime_common] AS Transporttijd;
LOAD*,
Date(Timestamp#([Date_common],'DD-MM-YYYY'),'WWWW') AS LongDayNames;
LOAD*,
Timestamp(Timestamp#([Date_common],'DD-MM-YYYY'),'DD-MM-YYYY') AS Common_Date;
LOAD
'1' as #Nr_transporten,
@1 AS "Transport ID",
@2 AS TransportCode,
Timestamp(Timestamp#(@4,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss') AS "EHB in Time",
Timestamp(Timestamp#(@4,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY') AS Date_common,
Timestamp(Timestamp#(@4,'YYYY.MM.DD hh:mm:ss'),'hh:mm') AS Starttime_common,
@5 AS "EHB in ST",
'St_'&@5 AS ST_Algemeen,
If(@5<25,1) As #CnaarZ,
If(@5>25,1) As #ZnaarC,
IF (@8 =0,''),IF(@8 =15,'St_15 Nam',IF(@8 =16,'St_16 Nam',IF(@8=22,'St_22 Nam',IF(@8=23,'St_23 Nam', IF(@8=24,'St_24 Nam'))))) As Namech,
@6 AS Destination,
Timestamp(Timestamp#(@7,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss') AS "EHB out Time",
Timestamp(Timestamp#(@7,'YYYY.MM.DD hh:mm:ss'),'hh:mm') AS Endtime_common,
@8 AS "EHB out ST",
'St_'&@8 AS ST_Algemeen1,
@5&','&@8 AS "Key EHB station",
@9 AS "Des Error",
@10 AS "Cart type",
If(@10 = 02,1) AS DC,
@11 AS "TR nr",
'TR'&@11 AS "Storings oort",
If((Timestamp(Timestamp#(@7,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss')-Timestamp(Timestamp#(@4,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss'))>0.0138888888888890, 1, 0) AS "op tijd/ te laat numeriek >20min", // 3 mogelijkheden var1>20min, Var2>30min, Var3>45min,
If((Timestamp(Timestamp#(@7,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss')-Timestamp(Timestamp#(@4,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss'))>0.0138888888888890, 'Te laat', 'Op tijd') AS "op tijd/ te laat >20min", // Verplaatst naar join hieronder
If((Timestamp(Timestamp#(@7,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss')-Timestamp(Timestamp#(@4,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss'))>0.0208333333333334, 1, 0) AS "op tijd/ te laat numeriek >30min",
If((Timestamp(Timestamp#(@7,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss')-Timestamp(Timestamp#(@4,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss'))>0.0208333333333334, 'Te laat', 'Op tijd') AS "op tijd/ te laat >30min",
If((Timestamp(Timestamp#(@7,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss')-Timestamp(Timestamp#(@4,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss'))>0.031251, 1, 0) AS "op tijd/ te laat numeriek >45min",
If((Timestamp(Timestamp#(@7,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss')-Timestamp(Timestamp#(@4,'YYYY.MM.DD hh:mm:ss'),'DD-MM-YYYY hh:mm:ss'))>0.031251, 'Te laat', 'Op tijd') AS "op tijd/ te laat >45min"
FROM $(vFile)
(txt, codepage is 28591, no labels, delimiter is ';', msq)
WHERE @2 > 0 and @2 < 88 and @6 > 0 ; //and @9 < 88 and @9] > 88;
If, as part of the load statement, you want a third field, Common_ST, which is equivalent to ST_Algemeen & ST_Algemeen1, then just have the following as an additional row in the load statement:
'St_'&@5&'St_'&@8 AS Common_ST
Notes: 1. These are fields not variables 2. You can't have say "ST_Algemeen & ST_Algemeen1 AS Common_ST" in the same load statement as where ST_Algemeen and ST_Algemeen1 are being created as they don't exist until the load statement has completed
