Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
here i have attached the excel file where i make a mokeup data
In the attached excel i have four cloumn
Year Month Day Counter
against each day i have counter as 0 and 1 where 1 is working days and 0 means non working days. Now based on excell data i want calculate no of days for each month and year
the out put result i have mention in excell please look and provided me a feed back.
Hi,
one solution might be:
LOAD Year,
Month,
Day,
Counter,
If(Counter,AutoNumber(RecNo(),Year&Month),0) as Output
FROM [https://community.qlik.com/servlet/JiveServlet/download/1240783-272119/Qlikview%20Test.xlsx] (ooxml, embedded labels, table is Sheet1);
hope this helps
regards
Marco
Try as below
FACT:
Load Year,
Month,
Day,
Counter,
if(Counter=0,0,OP1) as Output ;
LOAD Year,
Month,
Day,
Counter,
if(Year=Peek(Year) and Month = Peek(Month),rangesum(Peek(OP1),Counter),Counter) as OP1
FROM
Fullpath\Qlikview Test.xlsx
(ooxml, embedded labels, table is Sheet1);
Hi,
one solution might be:
LOAD Year,
Month,
Day,
Counter,
If(Counter,AutoNumber(RecNo(),Year&Month),0) as Output
FROM [https://community.qlik.com/servlet/JiveServlet/download/1240783-272119/Qlikview%20Test.xlsx] (ooxml, embedded labels, table is Sheet1);
hope this helps
regards
Marco
Try this
Table:
LOAD Year,
Month,
Day,
Counter
FROM
[..\..\Downloads\Qlikview Test.xlsx]
(ooxml, embedded labels, table is Sheet1);
FinalTable:
LOAD *,
If(Year = Previous(Year) and Month = Previous(Month), RangeSum(Peek('Output'), Counter), Counter) as Output
Resident Table
Order By Year, Month, Day;
DROP Table Table;
Or this
Table:
LOAD Year,
Month,
Day,
Counter
FROM
[..\..\Downloads\Qlikview Test.xlsx]
(ooxml, embedded labels, table is Sheet1);
FinalTable:
LOAD *,
If(Counter = 1, Output, 0) as FinalOutput;
LOAD *,
If(Year = Previous(Year) and Month = Previous(Month), RangeSum(Peek('Output'), Counter), Counter) as Output
Resident Table
Order By Year, Month, Day;
DROP Table Table;
Wouldn't we need to sort the data using a resident load in case the data is not sorted by Year, Month, Day or by Date in ascending order? Or will this still work?
hello Vipin
see you in Example
Gruß gerry
Hi Marco,
I have a 1 question if i have region for all day then how i can apply this
e.g - East - 1
West - 1
North - 1
South - 1
East - 2
West - 2
North - 2
South - 2
In Excel i forget 1 column which is region
Yes, you are right.
This solution only works for already sorted source tables.
regards
Marco
Great, also, did you just load from an Excel file attached to this post? Is that something we can do from QlikView?