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?
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)
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?
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.
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
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.
@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