Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

adding two fields on LOAD

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!.

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Try

RangeSum(dia30, dia60,dia90,dia120,dia150,diamas) as DeudaTotal

instead

View solution in original post

9 Replies
OmarBenSalem

Logically,your syntax is good; can u provide a sample excel file of this please; with aybe 10 rows?

antoniotiman
Master III
Master III

Try

RangeSum(dia30, dia60,dia90,dia120,dia150,diamas) as DeudaTotal

instead

Anonymous
Not applicable
Author

Yes, enclosed.

ramyasaiqv
Creator II
Creator II

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;

OmarBenSalem

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:

Capture.PNG

why isn't it always the case?

Anonymous
Not applicable
Author

That did the trick!!

Thanks a lot!...

antoniotiman
Master III
Master III

Probably some field is null.

Anonymous
Not applicable
Author

May be it has to do with the fact that data is being loaded from Excel? I do not know...

Thanks for your help...

Anonymous
Not applicable
Author

There are some null values on those fields...