14 Replies Latest reply: Apr 6, 2018 10:09 AM by Hudson Prado RSS

    Erro carregar base

    Hudson Prado

      Boa tarde,

      Estou tentando subir um xlsx para o QlikCloud e estou tendo o seguinte erro:

       

      erroqlik.JPG

       

      Há mais um arquivo de excel nessa conta. Porém, o campo competencia não existe na outra base.

       

      Se puderem ajudar...

      Vasculhei a internet, mas não encontrei solução.

        • Re: Erro carregar base
          THIAGO TEIXEIRA

          Hudson,

           

          Há campos com mesmo nome no script?

           

          Veja o help sobre Campos Derivado - https://help.qlik.com/pt-BR/sense/September2017/Subsystems/Hub/Content/Scripting/derived-fields.htm

            • Re: Erro carregar base
              Hudson Prado

              Tem. Mas, já subi arquivos diferentes com campos com nome igual e não tinha acontecido isso.

               

              Primeiro arquivo excel:

               

              [Planilha1]:

              LOAD

              [nome_emp],

              [nome],

              [sexo],

              Date([data_nascimento] ) AS [data_nascimento],

              [depto],

              [funcao],

              [idade],

              [salario],

              Timestamp([admissao] ) AS [admissao],

              [CentroCustos],

              Timestamp([DATA_AFAST] ) AS [DATA_AFAST],

              [Afastamento]

              FROM [lib://AttachedFiles/dados.xlsx]

              (ooxml, embedded labels, table is Planilha1);

               

              -----

               

              Segundo arquivo Excel:

               

              [Folha2018]:

              LOAD

              [i_empregados],

              [codi_emp],

              [nome_emp],

              [CNPJ],

              [matricula],

              [nome],

              [sexo],

              Date(Date#([data_nascimento], 'M/D/YYYY') ) AS [data_nascimento],

              [admissao],

              [CargoFuncao],

              [CentroCustos],

              [depto],

              [I_CALCULOS],

              [CodRubrica],

              [DescRubrica],

              [ValorRubrica],

              [ProventoDesconto],

              Date([competencia])

              FROM [lib://AttachedFiles/Folha2018.xlsx]

              (ooxml, embedded labels, table is Folha2018);

                • Re: Erro carregar base
                  THIAGO TEIXEIRA

                  Me referi a campos com mesmo nome na mesma tabela. Mas aí tá ok.

                   

                  Mostre o autocalendar que ele criou e altere o campo Date([competencia]) para

                  Date([competencia]) as competencia.

                    • Re: Erro carregar base
                      Hudson Prado

                      Segue, Thiago.

                       

                      [autoCalendar]:

                        DECLARE FIELD DEFINITION Tagged ('$date')

                      FIELDS

                        Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

                        Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),

                        Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),

                        Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),

                        Month($1) AS [Month] Tagged ('$month', '$cyclic'),

                        Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),

                        Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),

                        Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),

                        Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),

                        Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),

                        If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,

                        Year(Today())-Year($1) AS [YearsAgo] ,

                        If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,

                        4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,

                        Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,

                        If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,

                        12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,

                        Month(Today())-Month($1) AS [MonthRelNo] ,

                        If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,

                        (WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,

                        Week(Today())-Week($1) AS [WeekRelNo] ;

                       

                       

                      DERIVE FIELDS FROM FIELDS [competencia] USING [autoCalendar] ;

                • Re: Erro carregar base
                  THIAGO TEIXEIRA

                  Hudson,

                   

                  Remova o autocalendar e inclua o script de calendário mestre (retirado daqui da Qlik Community):

                   

                  Temp: 

                  Load 

                                 min(competencia) as minDate, 

                                 max(competencia) as maxDate 

                  Resident SUA_TABELA

                   

                  Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

                  Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

                  DROP Table Temp; 

                   

                  TempCalendar: 

                  LOAD 

                                 $(varMinDate) + Iterno()-1 As Num, 

                                 Date($(varMinDate) + IterNo() - 1) as TempDate 

                                 AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                   

                  MasterCalendar: 

                  Load 

                                 TempDate AS competencia

                                 week(TempDate) As Week, 

                                 Year(TempDate) As Year, 

                                 Month(TempDate) As Month, 

                                 Day(TempDate) As Day, 

                                 YeartoDate(TempDate)*-1 as CurYTDFlag, 

                                 YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                                 inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                                 date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                                 ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                                 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                                 WeekDay(TempDate) as WeekDay 

                  Resident TempCalendar 

                  Order By TempDate ASC; 

                  Drop Table TempCalendar;