Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
;
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,
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
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,
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
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,
Thank you @ArnadoSandoval for your answer, but unfortunately I can`t find any solution 😞
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,
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;
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.
@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
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 ...
Thanks,