9 Replies Latest reply: Nov 21, 2017 5:38 PM by Rene Dimarco

adding two fields on LOAD

Rene Dimarco Nov 21, 2017 4:59 PM

Hello,

This is a very simple question, but somehow I can´t find the answer...

I´m using Qlik Sense to visualize some data from a EXCEL file. There are several fields on the EXCEL file that show the amount of money owned for a particular length of time:

LOAD

FechaCorte,

lugar as OrigenFactura,

tipo,

rsocial1 as Vendedor,

code as NIT,

rsocial2 as Deudor,

fecha,

factura,

dia30 as deuda30Dias,

dia60 as deuda60Dias,

dia90 as deuda90Dias,

dia120 as deuda120Dias,

dia150 as deuda150Dias,

diamas as deudaMasde150Dias,

dia30 + dia60 + dia90 + dia120 + dia150 + diamas as DeudaTotal

FROM [lib://Soportes Recaudos (vliberal_rdimarco)/CarteraTotal.XLS]

(biff, embedded labels, table is [Cartera Total VL+PG$]);

So, I want to create a new field that shows the sum of all of these fields. I called that field "DeudaTotal" (TotalOwned). I did it like this:

dia30 + dia60 + dia90 + dia120 + dia150 + diamas as DeudaTotal

But somehow that new field is always Blank.

The other values load OK. Dia30 is the value of an invoice due for less than 30 days. Dia60 is between 30 and 60 days, and so forth.

Any help with the syntaxis of that sum is welcomed.

Thanks a lot!.

Re: adding two fields on LOAD

omar bensalem Nov 21, 2017 5:20 PM (in response to Rene Dimarco)

Logically,your syntax is good; can u provide a sample excel file of this please; with aybe 10 rows?
Like Show 0 Likes (0)

Actions
- Re: adding two fields on LOAD
  
  Rene Dimarco Nov 21, 2017 5:25 PM (in response to omar bensalem )
  
  Yes, enclosed.
  
  CarteraTotal_example.xls 30.5 K
  
  Like Show 0 Likes (0)
  
  Actions
Re: adding two fields on LOAD

Antonio Mancini Nov 21, 2017 5:23 PM (in response to Rene Dimarco)

Try
RangeSum(dia30, dia60,dia90,dia120,dia150,diamas) as DeudaTotal
instead
Like Show 2 Likes (2)

Actions
- Re: adding two fields on LOAD
  
  omar bensalem Nov 21, 2017 5:28 PM (in response to Antonio Mancini)
  
  logically with adding the fields, we can have the sum of them :
  LOAD
      col1,
      m1,
      m2,
      m3,
     ( m1+m2+m3) as somme,
      col2
  FROM [lib://Mariage/description.xlsx]
  (ooxml, embedded labels, table is Sheet2);
  result:
  
  why isn't it always the case?
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: adding two fields on LOAD
    
    Antonio Mancini Nov 21, 2017 5:37 PM (in response to omar bensalem )
    
    Probably some field is null.
    
    Like Show 0 Likes (0)
    
    Actions
  - Re: adding two fields on LOAD
    
    Rene Dimarco Nov 21, 2017 5:37 PM (in response to omar bensalem )
    
    May be it has to do with the fact that data is being loaded from Excel? I do not know...
    
    Thanks for your help...
    
    Like Show 0 Likes (0)
    
    Actions
  - Re: adding two fields on LOAD
    
    Rene Dimarco Nov 21, 2017 5:38 PM (in response to omar bensalem )
    
    There are some null values on those fields...
    
    Like Show 0 Likes (0)
    
    Actions
- Re: adding two fields on LOAD
  
  Rene Dimarco Nov 21, 2017 5:28 PM (in response to Antonio Mancini)
  
  That did the trick!!
  
  Thanks a lot!...
  
  Like Show 0 Likes (0)
  
  Actions
Re: adding two fields on LOAD

Ramya Sai Nov 21, 2017 5:26 PM (in response to Rene Dimarco)

This might work for you.

LOAD
FechaCorte,
    lugar as OrigenFactura,
    tipo,
    rsocial1 as Vendedor,
    code as NIT,
    rsocial2 as Deudor,
    fecha,
    factura,
    dia30 as deuda30Dias,
    dia60 as deuda60Dias,
    dia90 as deuda90Dias,
    dia120 as deuda120Dias,
    dia150 as deuda150Dias,
    diamas as deudaMasde150Dias,
sum( dia30 )+ sum(dia60) +sum( dia90) + sum(dia120) + sum(dia150) + sum(diamas) as DeudaTotal
FROM [lib://Soportes Recaudos (vliberal_rdimarco)/CarteraTotal.XLS]
(biff, embedded labels, table is [Cartera Total VL+PG$])

    Group by OrigenFactura, tipo, Vendedor, NIT, Deudor,     fecha, facture;
Like Show 0 Likes (0)

Actions

Go to original post