6 Replies Latest reply: Sep 26, 2018 12:32 AM by Mahitha M RSS

    Cross Table Help

    Mahitha M

      Hi Experts,

       

      Can any one please help me on below requirement.
      I have the source like below.
      I have to store Training 1, Training 2... in Training Column and all dates in [Completion Date] Column

      So I have tried the below code. But getting only 4 IDs in the result and missing 104 and 106 IDs because all Trainings are blank.

       

      Cross table.png

       

      [Training Details]:
      CrossTable(Training,CompletionDate,2)
      LOAD
          *
      FROM [lib://Source/Training Sample.xlsx]
      (ooxml, embedded labels, table is Sheet1);

       

      Result:

       

      Cross table result.pngThen I have tried the below code to get the blank ids as well.

      [Training Details]:

      CrossTable(Training,CompletionDate,2)

      LOAD

          ID,

          Name,

          if(len(Trim("Training 1"))>0,"Training 1",'Null')    as  "Training 1",

            if(len(Trim("Training 2"))>0,"Training 2",'Null')    as  "Training 2",

              if(len(Trim("Training 3"))>0,"Training 3",'Null')    as  "Training 3",

                if(len(Trim( "Training 4"))>0, "Training 4",'Null')    as  "Training 4",

                  if(len(Trim( "Training 5"))>0, "Training 5",'Null')    as  "Training 5"

       

      FROM [lib://Source/Training Sample.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

      Result:

      6IDS.png

       

      ISSUE:

       

      Here the issue is daily one new training will be added in the source file it has to be update dynamically in Qlik. When I have removed * its not updating dynamically and if I put * not able to apply the blank if logic.

       

      Please help me to get any newly added Training 6 column into source file it has to be updated dynamically and need to get all the IDs in UI.

       

      Thanks in advance.