Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am breaking my head on following issue. I tried join and concatenate but in table viewer i can't see correct results.I
got three billing months data as.
Dec :
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss
From DEC_2009
Jan:
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss
From JAN_2010
Feb:
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss
From FEB_2010
Later on I need to calculate Connection number,Should I concatenate or Join.
experts can you help me on this issue please.
Thanks,
JK
Concatenate
Concatenate
Hi,
Use Concatenate:
Dec:
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss
From DEC_2009;
Concatenate
Jan:
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss
From JAN_2010;
Concatenate
Feb:
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss
From FEB_2010
Use Concatenate as we have same columns across all tables,join is used when they are different columns.
The load will auto-concatenate the data to a single table containing the data from all source files because the field names are identical in each table being loaded. There is no need to add a concatenate command.
The table viewer should show one table containing the total number of rows fromDEC_2009, JAN_2010 and FEB_2010.
yes qlikview auto concatenates for same columns across tables and to force concatenate we use concatenate syntax
Hi Janaki,
No need to do Concatenate in your case as all the columns are same so, autoconcatenate will come into picture.
If you want to calculate sum(Connection_No) then it will give sum of total tables. But in case if you want to calculate each table, for example sum(Connection_No) for only Dec table, then you can't, to do that you need to add Flag for each table as I placed below. so you can use sum(Connection_No) per each table by using Set analysis.
Ex: To get DEC sum of Connection_No= sum({<Type={'Dec'}>}Connection_No).
To get Jan sum of Connection_No= sum({<Type={'Jan'}>}Connection_No).
To get Feb sum of Connection_No= sum({<Type={'Feb'}>}Connection_No).
Code: Just add one more column as below.
Dec:
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss,
From DEC_2009;
Jan:
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss,
From JAN_2010;
Feb:
Load
Connection_No,
Date,
Name,
Bill,
Arrears,
Power_consumed,
Loss,
From FEB_2010
Thank you,
Hi Raj ,
I tried your logic, I got 10000 records in each table .The results are coming as follows ,which is not true .Can you help me what to do here. PFA
Here I am not using sum FYI, Using Count.
Thanks,
JK
Load
Connection_No,
Date,
month(Date) AS month,
year(Date) As year,
Name,
Bill,
Arrears,
Power_consumed,
Loss
From WHATEVERTHEFILE;
no matter how many loads you perform, can even go automatically using a loop., Qview will concatenate them automatically for you. and then in expression just refer to the month and year as follows:
in a chart. dimension, year,month and put sum(Power_consumed) in an expression.
from the text boxes
='Dec ' & chr(13) & SUM({1 <month={12},year={2014}>} Power_consumed)
or
='Dec ' & chr(13) & COUNT({1 <month={12},year={2014}>} DISTINCT Connection_No )
above for showing number of connections numbers in 2014 dec.