Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vignesh_s
Creator
Creator

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
Frank_Hartmann
Master II
Master II

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;

View solution in original post

5 Replies
Anil_Babu_Samineni

PFA

Capture.JPG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Frank_Hartmann
Master II
Master II

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
Creator
Creator
Author

thanks

vignesh_s
Creator
Creator
Author

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

Anil_Babu_Samineni

Script attached

And expression RangeSum(value,Value1,Value2)

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)