Hi, I have a date field and id field in same table. I want to create a new column or new table whichever is suitable which contains all possible combination of id with date. The date should be from mindate to maxdate just like in master calendar. for example: Input: date,id 2022-08-23,5 2022-08-25,6 2022-08-26,5 Output: date,id,flag 2022-08-23,5,1 2022-08-23,6,0 2022-08-24,5,0 2022-08-24,6,0 2022-08-25,5,0 2022-08-25,6,1 2022-08-26,5,1 2022-08-26,6,0
How can we do this in qliksense. I have tried joins but was unable to get desired output.Another solution could be nested loops like outer loops should run from mindate till maxdate and in inner loop we will compare that is id present on this date so create column flag and mark 0 else 1 ,but I am unable to implement this code based on above scenario. Can anyone kindly guide me regarding this with actual implementation for which I can get above output as I am stuck since days on this task.. TIA
Input: load Date(Date#(date, 'YYYY-MM-DD')) as date, id inline [ date,id 2022-08-23,5 2022-08-25,6 2022-08-26,5 ];
// calc the min and max date MinMaxDate: load min(date) as min_date, Max(date) as max_date Resident Input;
LET v_min_date = peek('min_date'); LET v_max_date = peek('max_date');
// all dates between min and max Output: Load '$(v_min_date)' + iterno() -1 as date AutoGenerate 1 While '$(v_min_date)' + iterno() -1 <= '$(v_max_date)';
// full outer join with id Join (Output) load Distinct id Resident Input;
// flag left join (Output) load date, id, 1 as flag resident Input;
Hi, The above solution is partially correct but doesnot full fill my requirement. Let me tell you my actual requirement. I have got a table with inspectid ,statid and inspectdate. Inspectid is only generated when there is an inspection occured likewise inspectdate is also generated. The problem is to find stats that have zero inspection in a given time period or particular date. For example if a user selects date and other filter so in table it should be shown that on this date or time period there was no inspection done . Likewise if a user selects a year and a month and there are some days when inspection is done ( we can find this by inspectid ) and some days where inspection is not done so the table should only show that inspection is done in this month. Can anyone kindly guide me regarding this with actual implementation for which I can get above output as I am stuck since days on this task.
I think the suggestion from @maxgro goes in the right direction. This means creating at first a cartesian product from (calendar) dates and id's and merging then the existing data to it.
Your description of the real scenario is a bit short and you may need to extend the above shown logic, for example if not the global dates should be used else id-specific ones. Also you may need further load-statements after the left join to replace the non-matching NULL's with real data to make them visible and accessible respectively replacing the join approach with a mapping logic which includes the opportunity to set defaults by a non-matching.
