8 Replies Latest reply: Oct 5, 2017 7:18 AM by Victor GREFFET RSS

    Join Crosstable

    Victor GREFFET

      Hi Community,

      I am beginner in Qlikview development.

      In my app i wanted to join some "crosstabled" tables in order to have a specific structure.


      My loaded data in a qvd file:

       

      IDStep 1
      TargetDate
      Step 1
      ValidateDate
      Step 1
      ForecastDate
      Step 2
      TargetDate
      Step 2
      ValidateDate
      Step 2
      ForecastDate
      .....

      Step 5

      ForecastDate

      Nodatedatedatedatedatedate.....date
      Nodatedatedatedatedatedate.....date
      Nodatedatedatedatedatedate.....date
      No..............................

       

       

      what I want :

       

      IDSTEPTargetDatesValidateDatesForecastDates
      Nostepdatedatedate
      Nostepdatedatedate
      ..................


      What I did :

      [DATA]:

      Crosstable(No, TargetDates) LOAD

          ID,

      Step 1 TargetDate,

      Step 2 TargetDate,

      Step 3 TargetDate,

                                 Step 4 TargetDate,

                                 Step 5 TargetDate,


      FROM [lib://BXKD/DT_20171002_WK40.QVD]

      (qvd);

       

      Left join (DATA)

       

      // [VALIDATE_DATE]:

      Crosstable(No, ValidateDates) LOAD

          ID,

                               Step 1 ValidateDate,

                                Step 2 ValidateDate,

                                Step 3 ValidateDate,

                                 Step 4 ValidateDate,

                                 Step 5 ValidateDate,

      FROM [lib://BXKD/DT_20171002_WK40.QVD]

      (qvd);

       

      Left join (DATA)

       

      //[FORECAST_DATE]:

      Crosstable(No, ForecastDates) LOAD

      ID,

                                Step 1 ForecastDate,

                                Step 2 ForecastDate,

                                Step 3 ForecastDate,

                                 Step 4 ForecastDate,

                                 Step 5 ForecastDate,

      FROM [lib://BXKD/DT_20171002_WK40.QVD]

      (qvd);




      But after executing the script I have an error message :


      Capture.PNG



      Thank you for your help!

        • Re: Join Crosstable
          Eduardo DImperio

               Hi Victor,

          First, did you loaded separated cross table to be sure that's are in correct format to you?

          Second, Did you load all cross tables without joins?

          • Re: Join Crosstable
            kaan erisen

            Hi,

             

            Here is a sample code for you.

             

            temp:

            CrossTable(Type,Date,1)

            Load * Inline [

            No,Step1-TargetDate,Step1-ValidateDate,Step1-ForecastDate,Step2-TargetDate,Step2-ValidateDate,Step2-ForecastDate

            1,20170301,20170401,20170501,20170601,20170701,20170801

            2,20180301,20180401,20180501,20180601,20180701,20180801

            3,20190301,20190401,20190501,20190601,20190701,20190801

            ];

             

             

            Main:

            load distinct

            No,SubField(Type,'-',1) as Step

            Resident temp;

             

             

            left join(Main)

            load

            No,SubField(Type,'-',1) as Step,Date as TargetDate

            Resident temp

            where SubField(Type,'-',2)='TargetDate';

             

             

            left join(Main)

            load

            No,SubField(Type,'-',1) as Step,Date as ValidateDate

            Resident temp

            where SubField(Type,'-',2)='ValidateDate';

             

             

            left join(Main)

            load

            No,SubField(Type,'-',1) as Step,Date as ForecastDate

            Resident temp

            where SubField(Type,'-',2)='ForecastDate';

             

             

            Drop Table temp;

             

            crossJoin.png

              • Re: Join Crosstable
                Victor GREFFET

                This is awesome, thank you, i hope it will be usefull for the community.

                • Re: Join Crosstable
                  Victor GREFFET

                  I loaded my data from a qvd file, and after executing the script i have the correct structure but only empty field for dates :

                  Capture.PNG

                  I have some empty field in the source file...

                  Do you have an idea ?

                  Thank you again

                    • Re: Join Crosstable
                      kaan erisen

                      It seems the fields don't match for left joins. If you can share a sample of your data and the script, I can look into it.

                        • Re: Join Crosstable
                          Victor GREFFET

                          Hey kaan erisen,

                          it works perfectly with :

                           

                           

                          Main:

                          load distinct

                          Bridge_Key,

                          SubField(Type,'_',1) as Step

                          Resident temp;

                           

                           

                          left join(Main)

                          load

                          Bridge_Key,

                          SubField(Type,'_',1) as Step,Date as TargetDate

                          Resident temp

                          where Type like '*Target*';

                           

                           

                          left join(Main)

                          load

                          Bridge_Key,

                          SubField(Type,'_',1) as Step,Date as ValidateDate

                          Resident temp

                          where Type like '*Validated*';

                           

                           

                          left join(Main)

                          load

                          Bridge_Key,

                          SubField(Type,'_',1) as Step,Date as ForecastDate

                          Resident temp

                          where Type like '*Forecast*';

                          Drop Table temp;


                           

                          Thank you again for your precious help

                    • Re: Join Crosstable
                      kaan erisen

                      Hi,

                       

                      Here is a more generic way to achieve your goal. With this kind of script, you can get your desired output without adding the script even different datetype columns will be added the source.

                       

                      temp:

                      CrossTable(Type,Date,1)

                      Load * Inline [

                      No,Step1-TargetDate,Step1-ValidateDate,Step1-ForecastDate,Step2-TargetDate,Step2-ValidateDate,Step2-ForecastDate

                      1,20170301,20170401,20170501,20170601,20170701,20170801

                      2,20180301,20180401,20180501,20180601,20180701,20180801

                      3,20190301,20190401,20190501,20190601,20190701,20190801

                      ];

                       

                       

                      temp_datetype:

                      load distinct SubField(Type,'-',2) as datetype Resident temp;

                       

                       

                      Main:

                      load distinct

                      No,SubField(Type,'-',1) as Step

                      Resident temp;

                       

                       

                      FOR Each a in FieldValueList('datetype')

                        left join(Main)

                        load

                          No,

                          SubField(Type,'-',1) as Step,

                          Date as '$(a)'

                        Resident temp

                        where SubField(Type,'-',2)='$(a)';

                      NEXT a;

                       

                       

                      drop tables temp,temp_datetype;