Solved: Previous month sales in loading script - Qlik Community

razvan_brais · ‎2020-04-28

Hy guys. Hope you can help me.

I need to load from script in the same table current month sales , but I need to load previous month sales too.

The script looks something like this :

LOAD

CLIENT_ID,
CLIENT_NAME,
LOCATION_ID,
ApplyMap('MapLocations', Month, 'no_location') AS LOCATION_NAME,
SEGMENT,
BUSSINES_FLG,
NPL,
RATING,
MakeDate([YEAR], [Month]) AS HIST_DATE,
NUM([Month]) AS No_Month,
Month(MakeDate(2000,NUM([Month]), 1)) AS MonthMonth,
SALES,
Previous(SALES) as PREV_SALES

FROM TABLE;

The problem is when I`m comparing current sales for January with previous month December...

I don`t want to do this in SetAnalysis , I would like to have them calculated from the loading script.

Can you please help me with some hints?

Thank you,

Razvan

Vegar · ‎2020-05-03

In my script the values of January became the previous month value for february, thats why you get more dates thant what exists in your dec-jan sample data. In a larger sample the dates will inlign correcty to the following periods.

It you find the data disk space to increase to much when addining the prev month transactions then you could always use join like this. You will also get rid of the additonal HIST_DATE values if you choose to use LEFT join in your script.

Sample:
LOAD CLIENT_ID, 
     LOCATION_ID, 
     SEGMENT, 
     No_Month,
     HIST_DATE , 
     SALES as SALES
FROM
  SampleData.xlsx
  (ooxml, embedded labels, table is Sheet1)
;

LEFT JOIN (Sample)
LOAD CLIENT_ID, 
     LOCATION_ID, 
     SEGMENT, 
     num( month(addmonths(HIST_DATE, 1))) as Month_No,
     addmonths(HIST_DATE, 1) as HIST_DATE,  
     SALES as SALES_PREVIOUS
FROM
  SampleData.xlsx
  (ooxml, embedded labels, table is Sheet1)
;

Vegar
Qlik Community MVP

View solution in original post

ArnadoSandoval · ‎2020-05-04

Hi @razvan_brais

Earlier today I reviewed @Vegar script, it did not work for me with the LEFT JOIN (Sample) but it did when I changed the join with OUTER JOIN (Sample); it works still requiring some adjustments (take a look at this article explaining JOINS, it has small example on each type of joins Understanding Join-Keep-and-Concatenate ) because those December-2019 clients appear on January-2020 but with some nulls (the article clearly shows the behaviour), so I added the logic to resolve those nulls.

WARNING: Any client with transactions gap between months will get a record filling the gap, I did not test the scenario when the gap is bigger than one month.

Here is my script, I preload the data into TABLE in a previous section, here is the script:

NoConcatenate 

TEMP: // <-- load the data to a temp table because of the nulls.
Load 
    CLIENT_ID,    
    LOCATION_ID,    
    SEGMENT,   
    HIST_DATE,
    No_Month,    
    MONTHNAME(MAKEDATE(Year(HIST_DATE),Month(HIST_DATE)) ) AS Period,
    SALES AS SALES
Resident TABLE;

Outer Join (TEMP)   // <---- Outer Join instead of Left Join

Load 
    CLIENT_ID,    
    LOCATION_ID,    
    SEGMENT,   
    AddMonths(HIST_DATE, 1) As HIST_DATE,
    num( month(addmonths(HIST_DATE, 1))) as Month_No,
    SALES AS PREV_SALES
Resident TABLE;

NoConcatenate

// now the script handling the nulls.
DATA:
Load
    CLIENT_ID,    
    LOCATION_ID,    
    SEGMENT,   
    HIST_DATE,
    If(IsNull(No_Month), Month(HIST_DATE), No_Month) As No_Month,  
    If(IsNull(Period), MONTHNAME(MAKEDATE(Year(HIST_DATE),Month(HIST_DATE)) ), Period) As Period,
    SALES,
    PREV_SALES
Resident TEMP;

Drop Table TEMP;

Store DATA into [lib://QVDs/Results_v2.qvd] (qvd);

Drop Table TABLE;

You can give it to this script.

The script fills all the gaps (missing months) you will end with a bigger size QVD. I do not know how large for yours, locally I got 5 extra Kb.

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

View solution in original post

razvan_brais · ‎2020-05-05

Hy guys,

Finally I managed to solve the issue. It helped a lot your suggestions and solution.

I have user @Vegar script to get PREV_SALES.

The final script is the one bellow . Based on the PREV_SALES I needed to calculate variation of sales between current month and prev_month.

Now I don`t know what comment to mark as solution , because all of you helped me to achive to the final script 🙂

Temp_Data_1:
LOAD
    CLIENT_ID,    
    LOCATION_ID,    
    SEGMENT,   
    YEAR,            // <---- this column was introduced
    No_Month, 
    Date(MonthName(MAKEDATE(YEAR,No_Month) )) AS HIST_DATE,
    SALES 
FROM TABLE;


join(Temp_Data_1)
LOAD
    CLIENT_ID,    
    LOCATION_ID,    
    SEGMENT,   
    AddMonths(HIST_DATE,1) AS HIST_DATE,  
    SALES as PREV_SALES
Resident Temp_Data_1;

//getting max of Date from table with SALES and PREV_SALES and store it in a variable
Max_Month:
Load Max(HIST_DATE) as Max_Date
Resident Temp_Data_1;

Let vMaxMonth=Peek('Max_Date');
Drop Table Max_Month;

//loading all the data except de extra month that has been added to the table
NoConcatenate
Final:
Load CLIENT_ID,
     LOCATION_ID,
     SEGMENT,
     HIST_DATE,
     SALES,
     PREV_SALES,
     if(Len(SALES)<1,0,SALES) - if(Len(PREV_SALES)<1,0,PREV_SALES) as VARIATION
 Resident Temp_Data_1
 where Date(HIST_DATE) <Date($(vMaxMonth))
;
drop Table Temp_Data_1;

Thank you again for your help,

Razvan

View solution in original post

ArnadoSandoval · ‎2020-04-28

Hi @razvan_brais

This script is close to give you the solution, it is not bringing the previous month sales for any month of January, I did not include that bit of code for you to complete it.

//
// Load your data to a Temp_Data table
//
Temp_Data_1:
LOAD
    CLIENT_ID,
    CLIENT_NAME,
    LOCATION_ID,
    ApplyMap('MapLocations', Month, 'no_location') AS LOCATION_NAME,
    SEGMENT,
    BUSSINES_FLG,
    NPL,
    RATING,
    MakeDate([YEAR], [Month]) AS HIST_DATE,
    NUM([Month]) AS No_Month,
    Month(MakeDate(2000,NUM([Month]), 1)) AS MonthMonth,
    SALES 
//     Previous(SALES) as PREV_SALES
FROM TABLE; 

// Order your data by Location_ID, Client_Id, Hist_Date, No_Month

Temp_Data_2:
LOAD
    RowNo()         As Record_No,
    CLIENT_ID,
    CLIENT_NAME,
    LOCATION_ID,
    LOCATION_NAME,
    SEGMENT,
    BUSSINES_FLG,
    NPL,
    RATING,
    HIST_DATE,
    No_Month,
    MonthMonth,
    SALES,
//     Previous(SALES) as PREV_SALES
Resident Temp_Data_1
ORDER BY
    LOCATION_ID,
    CLIENT_ID,
    HIST_DATE,
    NO_MONTH;

// we do not need Temp_Data_1

Drop Table Temp_Data_1;

// Bring previous months sales

DATA:
LOAD 
    RowNo()
    CLIENT_ID,
    CLIENT_NAME,
    LOCATION_ID,
    LOCATION_NAME,
    SEGMENT,
    BUSSINES_FLG,
    NPL,
    RATING,
    HIST_DATE,
    No_Month,
    MonthMonth,
    SALES,
    If( Record_No = 1, 0, if(PREV_CLIENT_ID   = CLIENT_ID   and 
                             PREV_LOCATION_ID = LOCATION_ID and 
                             PREV_HIST_DATE   = HIST_DATE, Peek('SALES', Num#(Record_No - 1), SALES), 0) As PREV_SALES;

LOAD *,
     If( Record_No = 1, 0, Peek('CLIENT_ID', Num#(Record_No - 1), SALES)) As PREV_CLIENT_ID,
     If( Record_No = 1, 0, Peek('LOCATION_ID', Num#(Record_No - 1), SALES)) As PREV_LOCATION_ID,
     If( Record_No = 1, 0, Peek('HIST_DATE', Num#(Record_No - 1), SALES)) As PREV_HIST_DATE,
     If( Record_No = 1, 0, Peek('NO_MONTH', Num#(Record_No - 1), SALES)) As PREV_NO_MONTH'  
Resident Temp_Data_2;

// We do not need Temp_Data_2

Drop Table Temp_Data_2;

Hope this helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

razvan_brais · ‎2020-04-29

Hy , @ArnadoSandoval ,

Thank you for your answer . This helped a lot. I just made some adjustments to the script and it`s working fine.

The only problem appears when I`m trying to compare January-2020 with December-2019. My guess is because there might be some differences between CLIENTS ( I might have less clients in JANUARY than in December) , and the script does the calculations only for those that are in JANUARY.

Or there is some other things?

Thank you again,

Razvan

ArnadoSandoval · ‎2020-04-29

Hi @razvan_brais

You need to improve the condition, on Januaries the current year and previous year are different, you need to introduce this logic into the condition already there.

Hope the hint helps,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

razvan_brais · ‎2020-04-29

Thank you @ArnadoSandoval for your answer, but unfortunately I can`t find any solution 😞

ArnadoSandoval · ‎2020-04-29

@razvan_brais

Did you try to improve LOAD statement's If condition? if you did, would you share the code.

Can you post some sample data? or share small Excel file sample.

Regards,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.

razvan_brais · ‎2020-04-29

This is the code I wrote.

I followed your sample code and modified it a bit.

I can`t attach any sample file because I`m not allowed.

Temp_Data_1:
LOAD
CLIENT_ID,
CLIENT_NAME,
LOCATION_ID,
ApplyMap('MapLocations', Month, 'no_location') AS LOCATION_NAME,
SEGMENT,
BUSSINES_FLG,
NPL,
RATING,
MakeDate([YEAR], [Month]) AS HIST_DATE,
NUM([Month]) AS No_Month,
Month(MakeDate(2000,NUM([Month]), 1)) AS MonthMonth,
SALES
FROM TABLE;

Temp_Data_2:
LOAD
RowNo() As Record_No,
CLIENT_ID,
CLIENT_NAME,
LOCATION_ID,
LOCATION_NAME,
SEGMENT,
BUSSINES_FLG,
NPL,
RATING,
HIST_DATE,
No_Month,
MonthMonth,
SALES,

Resident Temp_Data_1
ORDER BY
LOCATION_ID,
CLIENT_ID,
//I ADDED THIS IN ORDER BY
SEGMENT,
HIST_DATE,
NO_MONTH;

Drop Table Temp_Data_1;

DATA:
LOAD
RowNo()
CLIENT_ID,
CLIENT_NAME,
LOCATION_ID,
LOCATION_NAME,
SEGMENT,
BUSSINES_FLG,
NPL,
RATING,
HIST_DATE,
No_Month,
MonthMonth,
SALES,
If( Record_No = 1, 0, if(PREV_CLIENT_ID = CLIENT_ID and
PREV_LOCATION_ID = LOCATION_ID and
//changed WITH PREV_SEGMENT
PREV_SEGMENT = SEGMENT , Peek('SALES'), 0) As PREV_SALES;

LOAD *,
If( Record_No = 1, 0, Peek('CLIENT_ID')) As PREV_CLIENT_ID,
If( Record_No = 1, 0, Peek('LOCATION_ID')) As PREV_LOCATION_ID,
If( Record_No = 1, 0, Peek('HIST_DATE')) As PREV_HIST_DATE,
If( Record_No = 1, 0, Peek('NO_MONTH')) As PREV_NO_MONTH'
Resident Temp_Data_2;

Drop Table Temp_Data_2;

razvan_brais · ‎2020-04-29

I have made a count over CLIENT_ID and I observed that in 2020 i have a number of , lets say 100 000 of clients , and in 2019 I have a lot more clients , lets say 150 000 clients.

When I`m selecting January 2020 it olny shows values for 100 000 clients , and PREVIOUS_SALES is calculated only for those clients.

razvan_brais · ‎2020-04-30

@ArnadoSandoval , i could modify the if statement something like this :

if(Record_No = 1,0, if(PREV_CLIENT_ID = CLIENT_ID and
PREV_LOCATION_ID = LOCATION_ID and
PREV_SEGMENT = SEGMENT
, if(No_Month<> 1 ,Peek('SALES'),'here I don`t know how can i refer to December values') )) as PREV_SALES,

Can you please help me?

Thank you

ArnadoSandoval · ‎2020-04-30

@razvan_brais

I did not notice the introduction of the SEGMENT column as part of the logic, I did not include it in my previous script; that could be part of the problem; Would you please share with me your current script? (I will add a picture illustrating the steps) also, Would you please show me your data, either attach an Excel file with small sample, or take an screenshot.

I think the issue you are having is with the introduction of the SEGMENT column.

This is how to include the script in your reply ...

Click on the ... (3 dots)
Click on the </> you will get a pop-up windows/form, paste your script in there and click OK.

Thanks,

Arnaldo Sandoval
A journey of a thousand miles begins with a single step.