Re: How to load a table with "date from" - "date t... - Qlik Community

Report Inappropriate Content · ‎2012-12-13

Hello!

I try to read a table contract information. This table looks like:

datefrom, dateto, monthvalue

01.01.2012, 31.12.2012, 1234,56

The output should be look like:

01.2012, 1234,56

02.2012, 1234,56

03.2012, 1234,56

...

Does someone has an idea, how to solve that?

nirav_bhimani · ‎2012-12-13

HI,

You can use if statement with condition.

try this

If( Date>='01.01.2012' AND Date<='31.12.2012',monthvalue,null())

Hope this will help you.

Regads,

Nirav Bhimani

sujeetsingh · ‎2012-12-13

Please explain inbreif..

And what you mean by output.(in some chart or table)

Report Inappropriate Content · ‎2012-12-13

Hi,

use this script:

Temp:
Load
Date#(DateFrom,'DD.MM.YYYY') As DateFrom,
Date#(DateTo,'DD.MM.YYYY') As DateTo,
Values
;
LOAD * INLINE [
    DateFrom, DateTo, Values
    01.01.2012, 30.06.2012, "1234,56"
     01.07.2012, 31.12.2012, "4321,56"
];

😧
NoConcatenate LOAD
Date(num(DateFrom) + IterNo(),'DD.MM.YYYY') As DATE,
Month(Date(num(DateFrom) + IterNo(),'DD.MM.YYYY')) AS MONTH,
Values
Resident
Temp
While num(DateFrom) + IterNo() <= num(DateTo)
;

Regards,

Niranjan M

Report Inappropriate Content · ‎2012-12-13

Sorry, i think my description was not good.

The table has contract information. The example contract has date from 1. January 2012

to 31. December 2012. And the contract-payment is 123 per month. In year 2013 the customer

has to pay more: 456 for the contract.

datefrom           dateto                value
01.01.2012        31.12.2012       123
01.01.2013        31.12.2013       456

The goal is to become a table which looks like the example. Means every period (month+year) with the

value of the month

period          value
01.2012       123
02.2012       123
03.2012       123
...
12.2012      123
01.2013       456
02.2013      456
03.2013      456
...
12.2013      456

Report Inappropriate Content · ‎2012-12-13

Hi,

use this modiefied Script:

Temp:
Load
Date#(DateFrom,'DD.MM.YYYY') As DateFrom,
Date#(DateTo,'DD.MM.YYYY') As DateTo,
Values
;
LOAD * INLINE [
    DateFrom, DateTo, Values
    01.01.2012, 31.12.2012, 123
     01.01.2013, 31.12.2013, 456
];

😧
NoConcatenate LOAD
Month(DateFrom) + IterNo()-1 & '.' & Year(DateFrom) As YEAR_MONTH,
Year(DateFrom) As YEAR,

//Date(num(DateFrom) + IterNo(),'DD.MM.YYYY') As DATE,
//Month(Date(num(DateFrom) + IterNo(),'DD.MM.YYYY')) AS MONTH,
Values
Resident
Temp
While num(MakeDate(Year(DateFrom),Month(DateFrom) + IterNo()-1,Day(DateFrom))) <= num(DateTo)
;

DROP Table Temp;

NiranJan M.

Report Inappropriate Content · ‎2012-12-13

Hi,

use below script..

Table:

load date(datefrom,'MM.YYYY') as Period,

value

from tablename where date(datefrom,'DD.MM.YYY)>='01.01.2012' and date(datefrom,'DD.MM.YYY)<='01.12.2012';

Hope it helps you!!..

Report Inappropriate Content · ‎2012-12-14

This was a big help - thank you!

It works fine, but if i use a date-range over two years, the script only writes the first year.

DateFrom, DateTo, Values

01.01.2012, 31.07.2013, 123
01.08.2013, 31.12.2013, 456

EDIT:

Now i found a solution: http://community.qlik.com/message/36659#36659

How to load a table with "date from" - "date to"