Help creating a table with populated dates

[#1]

Hey everybody,

I currently have a table which is something like this:

ID	Expense_type	Expense_Ammount	Date
1	Houskeepin	100	01/01/2015
1	Electricity	200	01/01/2015
1	Car	300	01/01/2015
1	Parking	400	01/01/2015

The date is always the first of every month and i have many ID (for each person in the company) and many types...

What i need to do is create a table that will hold the sum for every day for each expense_type of each id's from the begining of each year until the chosen date.

Is there a way to use the script editor to populate all the missing dates?

for example:

ID	type	ammount	date
1	Houskeeping	100	01/01/2015
1	Houskeeping	100	02/01/2015
1	Houskeeping	300(200 was added)	03/01/2015
1	Houskeeping	300	04/01/2015

Hope you can help me figure this out! Thanks!!!

[sunny_talwar]

You can definitely do it. Do you have a sample you want to share with your desired output? Above I see desired output, but the data to produce that output isn't complete.

[jduenyas]

Set your dimensions as ID, Type, Date (If the date is always the first of the month) and any other criteria you want to see.

In the expressions set the total as  Sum(Amount)

[Colin-Albert · Accepted solution]

This document has details on creating missing data.  Look at the examples on using Peek to populate missing dates.

Generating Missing Data In QlikView

[maxgro]

an example

LET vChosenDate = MakeDate(2015, 1, 21);

Source:

load * inline [

ID, Expense_type, Expense_Ammount, Date

1, Houskeepin, 100, 01/01/2015

1, Electricity, 200, 01/01/2015

1, Car, 300, 01/01/2015

1, Parking, 400, 01/01/2015

2, Houskeepin, 102, 20/01/2015

];

Tmp:

load min(Date) as MinDate, '$(vChosenDate)' as MaxDate Resident Source;

Dates:

NoConcatenate load date(MinDate + IterNo() -1) as Date

Resident Tmp

While (MinDate + IterNo() -1) <= MaxDate;

DROP Table Tmp;

Final:

LOAD Distinct ID Resident Source;

join (Final) load Distinct Expense_type Resident Source;

join (Final) load Distinct Date Resident Dates;

Left join (Final) LOAD ID, Expense_type, Date, Expense_Ammount Resident Source;

DROP Table Dates, Source;

[MarcoWedel]

Hi,

maybe one solution could be:

tabExpense:
LOAD RecNo() as ID,
    Pick(Ceil(Rand()*4),'Housekeeping','Electricity','Car','Parking') as Expense_Type,
    AddMonths(MakeDate(2014),Floor(Rand()*24)) as Date,
    Ceil(Rand()*500,10) as Expense_Amount
AutoGenerate 1
While Rand()>0.2;

tabCalendar: 
LOAD *, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year;   
LOAD AddMonths(MinDate,IterNo()-1) as Date 
While AddMonths(MinDate,IterNo()-1) <= MaxDate; 
LOAD Min(Date) as MinDate, 
    Max(Date) as MaxDate 
Resident tabExpense;

tabAsOfDate:
LOAD AddMonths(Date,IterNo()-1) as AsOfDate,
    Date
Resident tabCalendar
While IterNo()<=13-Month(Date);

see also: The As-Of Table

hope this helps

regards

Marco

[#7]

This seems to be going the right way...

Now i want to add another field which would be a running total (expenses_sum perhaps).

So that for every day in the Final table i would also have a field which is the running total of its year up to that day.

How can i achieve this?

[#8]

This document is absouloutly fantastic for begginers like me!

Tell me where can i find more excelent tutorials like this?

[maxgro]

hic

edit

Marcus  Marcus_Sommer‌  too has a lot of useful links, this is one of them

Get started with developing qlik datamodels