9 Replies Latest reply: Jul 22, 2013 11:03 AM by Erlen Rosa RSS

    Problem in Load multiples files

      I'm loading multiples files in a loop and this create multiples qlikview's table.

      table.PNG

       

      How avoid this and create only one table?

        • Re: Problem in Load multiples files
          Kaushik Solanki

          Hi,

           

               Kindly post your script.

           

               You need to use the concatenate keyword to concatenate the tables, and when you do this you will not find the synthetic table.

           

          Regards,

          Kaushik Solanki

          • Re: Problem in Load multiples files
            Marcus Sommer

            Drop your temp tables:

             

            drop tables Temp3, Temp3-1, Temp3-2, Temp3-3;

             

            - Marcus

            • Re: Problem in Load multiples files

              This is the script:

               

              Directory C:\;                

              For each TxtFile in filelist ('*.txt')

               

                                                    Temp1:

                                              LOAD @2, @3, @4, @5, @6, @7, @8, @9,

                                              if(peek(@2) <> @2 and @2 <> 'J100', RowNo(), if(isnull(peek(ID)), 1, peek(ID)) ) as ID

                                              FROM $(TxtFile)(txt, codepage is 1252, no labels, delimiter is '|', msq)

                                              WHERE(@2 = 'J100' or @2 = 'J005');

               

                                              Temp3:

                                              LOAD @3 as Date_Start, @4 as Date_End, ID   

                                              RESIDENT Temp1 WHERE @2 = 'J005'; 

                                              left join LOAD ID, num(@4) as Nível, @6 as Descrição, num(@7) as Valor

                                              RESIDENT Temp1 WHERE @2 = 'J100';

                                             

                   

                                              drop field ID;

                                              drop table Temp1;                               

               

              Next TxtFile;

                • Re: Problem in Load multiples files
                  Kaushik Solanki

                  Hi try this.

                   

                  Directory C:\;                

                  For each TxtFile in filelist ('*.txt')

                   

                                                        Temp1:

                                                  LOAD @2, @3, @4, @5, @6, @7, @8, @9,

                                                  if(peek(@2) <> @2 and @2 <> 'J100', RowNo(), if(isnull(peek(ID)), 1, peek(ID)) ) as ID

                                                  FROM $(TxtFile)(txt, codepage is 1252, no labels, delimiter is '|', msq)

                                                  WHERE(@2 = 'J100' or @2 = 'J005');

                  Next TxtFile;


                  For each TxtFile in filelist ('*.txt')
                                                  Temp3:

                                                  LOAD @3 as Date_Start, @4 as Date_End, ID   

                                                  RESIDENT Temp1 WHERE @2 = 'J005'; 

                                                  left join LOAD ID, num(@4) as Nível, @6 as Descrição, num(@7) as Valor

                                                  RESIDENT Temp1 WHERE @2 = 'J100';


                  Next TxtFile;                              

                  Right join (Temp3)
                  Load * resident Temp1;


                  drop table Temp1;                               

                   

                   

                   

                  Regards,

                  Kaushik Solanki

                • Re: Problem in Load multiples files

                  This are the files. So it becomes easier to you help me.

                    • Re: Problem in Load multiples files
                      Kaushik Solanki

                      Hi,

                       

                           Try this script

                       

                      For each TxtFile in filelist ('*.txt')
                            
                             Temp1:
                             LOAD @2, @3, @4, @5, @6, @7, @8, @9,
                             if(peek(@2) <> @2 and @2 <> 'J100', RowNo(), if(isnull(peek(ID)), 1, peek(ID)) ) as ID
                             FROM $(TxtFile)(txt, codepage is 1252, no labels, delimiter is '|', msq)
                             WHERE(@2 = 'J100' or @2 = 'J005');
                            
                          Next TxtFile;    

                        For each TxtFile in filelist ('*.txt')    
                            
                             Temp3:
                             LOAD @3 as Date_Start, @4 as Date_End, ID   
                             RESIDENT Temp1 WHERE @2 = 'J005'; 
                            
                            
                                        
                            
                        Next TxtFile;

                      left join (Temp3)
                      LOAD ID, num(@4) as Nível, @6 as Descrição, num(@7) as Valor
                          RESIDENT Temp1 WHERE @2 = 'J100';
                         
                         
                      drop field ID;
                             drop table Temp1;

                       

                      Regards,

                      Kaushik SOlanki