Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
pasayan19
New Contributor

Qlikview Variables

hello experts,

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;

                                   

 

          STORE NextFile INTO $(vQVD) (qvd);

 

          DROP TABLE NextFile;

                END IF

                NEXT

 

 

Transportlijsten:

LOAD *

FROM   [lib://Transportlijsten\*.qvd] (qvd);

 

CONCATENATE("FHVS meldingen$")

LOAD *

RESIDENT "Transportlijsten";     

 

DROP TABLE "Transportlijsten";   

1 Reply
rogerpegler
Contributor II

Re: Qlikview Variables

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