Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

vignesh_s
Contributor

date,mapping load

i have a following load inline table(1,2,3),i trying to get the out put in such a way that ,dates are same in all the table i need value feild should be added up i need to get 300 for each date,can i get the expression

table1:

LOAD * INLINE [

    Date, Value1

    13-07-2014 12:00:00 AM, 100

    14-07-2014 12:00:00 AM, 100

    15-07-2014 12:00:00 AM, 100

    16-07-2014 12:00:00 AM, 100

    17-07-2014 12:00:00 AM, 100

   

];

table2:

LOAD * INLINE [

    Date, Value2

    13-Jul-2014, 100

    14-Jul-2014, 100

    15-Jul-2014, 100

    16-Jul-2014, 100

    17-Jul-2014, 100

   

];

table3:

LOAD * INLINE [

    Date, value

    13/o7/2014, 100

    14/o7/2014, 100

    15/o7/2014, 100

    16/o7/2014, 100

    17/o7/2014, 100

   

];

output:

  

DateValue
13-Jul-2014300
14-Jul-2014300
15-Jul-2014300
16-Jul-2014300
17-Jul-2014300
1 Solution

Accepted Solutions
Highlighted
Frank_Hartmann
Honored Contributor II

Re: date,mapping load

if you need it at script level then try following code:

table1:

Load * , Date(Date#(Date,'DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY') as Date_New,

Value1 as Value;;

LOAD * INLINE [

    Date, Value1

    13-07-2014 12:00:00 AM, 100

    14-07-2014 12:00:00 AM, 100

    15-07-2014 12:00:00 AM, 100

    16-07-2014 12:00:00 AM, 100

    17-07-2014 12:00:00 AM, 100

  

];

Concatenate

table2:

Load * , Date(Date#(Date,'DD-MMM-YYYY'),'DD-MM-YYYY') as Date_New,

Value2 as Value;;

LOAD * INLINE [

    Date, Value2

    13-Jul-2014, 100

    14-Jul-2014, 100

    15-Jul-2014, 100

    16-Jul-2014, 100

    17-Jul-2014, 100

  

];

Concatenate

table3:

Load * , Date(Date#(Replace(Date,'o','0'),'DD/MM/YYYY'),'DD-MM-YYYY') as Date_New,

value as Value;

LOAD * INLINE [

    Date, value

    13/o7/2014, 100

    14/o7/2014, 100

    15/o7/2014, 100

    16/o7/2014, 100

    17/o7/2014, 100

  

];

NoConcatenate

Final:

Load Date_New, sum(Value) as Value Resident table1 Group by Date_New; DROP table table1;

5 Replies

Re: date,mapping load

PFA

Capture.JPG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Frank_Hartmann
Honored Contributor II

Re: date,mapping load

if you need it at script level then try following code:

table1:

Load * , Date(Date#(Date,'DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY') as Date_New,

Value1 as Value;;

LOAD * INLINE [

    Date, Value1

    13-07-2014 12:00:00 AM, 100

    14-07-2014 12:00:00 AM, 100

    15-07-2014 12:00:00 AM, 100

    16-07-2014 12:00:00 AM, 100

    17-07-2014 12:00:00 AM, 100

  

];

Concatenate

table2:

Load * , Date(Date#(Date,'DD-MMM-YYYY'),'DD-MM-YYYY') as Date_New,

Value2 as Value;;

LOAD * INLINE [

    Date, Value2

    13-Jul-2014, 100

    14-Jul-2014, 100

    15-Jul-2014, 100

    16-Jul-2014, 100

    17-Jul-2014, 100

  

];

Concatenate

table3:

Load * , Date(Date#(Replace(Date,'o','0'),'DD/MM/YYYY'),'DD-MM-YYYY') as Date_New,

value as Value;

LOAD * INLINE [

    Date, value

    13/o7/2014, 100

    14/o7/2014, 100

    15/o7/2014, 100

    16/o7/2014, 100

    17/o7/2014, 100

  

];

NoConcatenate

Final:

Load Date_New, sum(Value) as Value Resident table1 Group by Date_New; DROP table table1;

vignesh_s
Contributor

Re: date,mapping load

thanks

vignesh_s
Contributor

Re: date,mapping load

since im using trail version of QV, im nt able to open the file u sent,can u give the expresion in text

Re: date,mapping load

Script attached

And expression RangeSum(value,Value1,Value2)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)