1 Reply Latest reply: Dec 5, 2012 9:44 AM by guillaumek RSS

    Qlikview read line by line problem

      Hi, I have a problem to build my tables. I have in my data:

       

      1 File with:

           - SNumber

           - Other Field 1

           - Other Field 2

       

      Ex:

           Snumber1     Other Field 1 Other Field 2

           Snumber2    Other Field 3 Other Field 4

           ..... (Snumber is never 2 times the same)

       

      1 File With:

           - SNumber

           - Code Dep

           - Code Step

           - Label Dep

           - Date Step

       

      Ex:

           Snumber1     Code Step 1     Code Dep 1     Label Dep 1    Date Step 1

           Snumber1     Code Step 2     Code Dep 2     Label Dep 2    Date Step 2

           Snumber1     Code Step 3     Code Dep 1     Label Dep 1    Date Step 1

           Snumber1     Code Step 3     Code Dep 4     Label Dep 4    Date Step 4

           Snumber1     Code Step 3     Code Dep 5     Label Dep 5    Date Step 5

           Snumber2     Code Step 2     Code Dep 1     Label Dep 1    Date Step 1

           Snumber2     Code Step 2     Code Dep 3     Label Dep 3    Date Step 3

           Snumber2     Code Step 4     Code Dep 4     Label Dep 4    Date Step 4

           ..... (Snumber is always 14 times cause there is 14 step.

                The Step can be more than one time cause it can have some dates - Label - Dep for one step.

                The date can be empty and Label and code Dep too)

       

      So, I would like to create a Flag, saying if I have a Date Step 2 for Code Step 2, My flag will be T1 else 0.

      I have to do the sum of this flag fot find how many Snumber pass the Step.

      It works, BUT when I have more than one Code Step for one Snumber, It count me 3 times the Flag if I have 3 Code Dep for the same Code Step......

       

      My flag have to work if it find minimum 1 Date Step for the Step = 1. But not put me 1 for each one because Qlikview read line by line and don't memorize the line before.

       

      This is my code:

      D_TEST:

        LOAD

        Snumber,

             if(isnull([Date Step]),

              Replace(0, $(E4), $(E4)),

               Replace(1, $(E4), $(E4))

             ) AS %FLAG_DateStepP1

        FROM $(vFile) (qvd)

        WHERE [Code Step] = 4;

       

        JOIN LOAD

        Snumber,

             if(isnull([Date Step]),

              Replace(0, $(E6), $(E6)),

               Replace(1, $(E6), $(E6))

             ) AS %FLAG_DateStepP2

        FROM $(vFile) (qvd)

        WHERE [Code Step] = 6;

       

      For make the code works, I have to replace on the last line WHERE [Code Step] = 6; by WHERE [Code Step] = 6 AND [Code Dep] = 3;

      But I don't want to filter the [Code Dep] cause it's not the same each time for each Snumber ....

       

      Can you help me please?

       

      Thanks by advance