topic Re: Help creating a table with populated dates in QlikView

Help creating a table with populated dates

Tue, 12 Jan 2016 15:52:47 GMT

Hey everybody,

I currently have a table which is something like this:

Expense_type

Expense_Ammount

Date

Houskeepin

100

01/01/2015

Electricity

200

01/01/2015

Car

300

01/01/2015

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!!!

Re: Help creating a table with populated dates

sunny_talwar — Tue, 12 Jan 2016 17:26:49 GMT

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.

Re: Help creating a table with populated dates

jduenyas — Tue, 12 Jan 2016 17:41:25 GMT

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)

Re: Help creating a table with populated dates

Colin-Albert — Tue, 12 Jan 2016 17:49:52 GMT

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

Generating Missing Data In QlikView

Re: Help creating a table with populated dates

maxgro — Tue, 12 Jan 2016 19:06:18 GMT

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;

Re: Help creating a table with populated dates

MarcoWedel — Tue, 12 Jan 2016 23:30:06 GMT

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);

Re: Help creating a table with populated dates

Wed, 13 Jan 2016 07:52:19 GMT

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?

Re: Help creating a table with populated dates

Wed, 13 Jan 2016 09:19:08 GMT

This document is absouloutly fantastic for begginers like me!

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

Re: Help creating a table with populated dates

maxgro — Wed, 13 Jan 2016 09:59:48 GMT

hic

edit

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

Get started with developing qlik datamodels