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: 
MalcolmCICWF
Creator III
Creator III

Insert Rows for Missing Months

I'm not trying to fill in null or missing values to what I have, but instead trying to find the easiest way to create a row similar to the others with the missing TranMonth where there wasn't a transaction.  By each ID there are TranAmnt, The TranMonths are how many months from the Close_Date the Transaction took place. The red in the example below is what I'm trying to fill in a record for... is there a way to do this. Everything else just needs to be zeroed out, I just need to the TranMonths, ID, and FaceValue to make it to each row...

 

Any ideas? Is this something that can/should be done in Qlikview or is this really meant to be done on the table side of things prior to loading in? 

 

Capture1.PNG

1 Solution

Accepted Solutions
MalcolmCICWF
Creator III
Creator III
Author

I'll figure it out, thanks for your help

View solution in original post

9 Replies
edwin
Master II
Master II

if you get a distinct of ID and FaceValue, outer join that with all MONTHS, then you will have a table with DI, FaceValue and ALL MONTHS.  add a composite key ID and Month

add the same composite key ID and Month to your original data, then concat the made up table above where not exists the composite key (ID Month)  then you should have all the months for each ID and just the FaceValue where the original Month was not populated (all other fields null)

MalcolmCICWF
Creator III
Creator III
Author

By ALL MONTHS what do you mean? The months I have available? Because what you see in the example is what I have for months for that ID.

I'm supposed to take and load just  ID and Facevalue? Then, join that to ALL MONTHS? Like another table loaded with just months... and what for the key? How am I joining and ending up  with this table of ID, Facevalue, and All Months?

edwin
Master II
Master II

this is what i mean:

effectively your "MONTH" is combination of TRANDATE + TRANMONTH.  so if you have a distinct of these + ID + facevalue (ALLMONTHS in my script)  you have the skeleton of what needs to be inserted in place of missing rows.

in script, i simulated this table using inline but there must be a way for you to build this using your datamodel.

when you concatenate only the missing ID & months combination you effectively filled in all missing months. this is done by using NOT EXIST

in my sample data months 123567 are missing, hence evrything is null except for id, trandate, tranamount and facevalue.

edwin_0-1607902948366.png

 

edwin
Master II
Master II

here is one way to generate the ALL MONTHS assuming you have start and end dates 2/1/2019 to 12/1/2019  as example:

 

data:
load *, ID & '|' & TranDate & '|' & TranMonth as Key inline [
ID, Close Date, TranDate, TranMonth, Face Value, TranAmount
SLF1, 2/19/209, 5/1/2019, 4, 526.914, 2122
SLF1, 2/19/209, 9/1/2019, 8, 526.914, 428
SLF1, 2/19/209, 10/1/2019, 9, 526.914, 174
SLF1, 2/19/209, 11/1/2019, 10, 526.914, 344
SLF1, 2/19/209, 12/1/2019, 11, 526.914, 117
];

NoConcatenate
IDS:
load distinct ID, [Face Value] resident data;

NoConcatenate
load
date(AddMonths(date('2/1/2019'),Iterno()-1)) as TranDate, iterno() as TranMonth
Resident IDS
while AddMonths(date('2/1/2019'),Iterno()-1)<=date('12/1/2019');


drop table data, IDS;

 


of course your code will depend on how your datamodel looks like

MalcolmCICWF
Creator III
Creator III
Author

So instead of a static date like you example, do I replace with the TranDate since the data may not always have the same min/max? Do I use Min/Max in this area to get that?

edwin
Master II
Master II

one way is to use variables, determine you min max first, save them in variables and inject it into the script.  this is normally how i load my calendar - i get the min and max of the dates in the fact table load them into variables and load my calendar

where DATE >= date('$(minDate)') and DATE <= date('$(maxDate)')

 

so it really depends on your datamodel and itll be impossible for anyone else to guess

MalcolmCICWF
Creator III
Creator III
Author

I get  Error: Aggregation functions not allowed here. After this below lines... i assume maybe because I took the Facevalue out? That field is actually from a 2nd table and only makes it into that straight table because there because of the ID key that links them.... but it's not actually in my data pool at this point. 

edwin
Master II
Master II

@MalcolmCICWF  when you say After this below lines are you referring to code you posted somewhere?  were you getting the min and max of the dates?  of course that is allowed in the script - you just have to code it he right way.  if you share code or QVW it will be easier.  also you need to be clear what values come default with the missing months.  in your picture face value 526914 appears to be a default.  anyone else can only rely on what you share

MalcolmCICWF
Creator III
Creator III
Author

I'll figure it out, thanks for your help