Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
niha
Creator II
Creator II

Preceding or Resident Load

Hello,

I have the following table .  First three table are for Quarter 3, quarter 2 and Quarter 1 data. I concatenated all to get a one table say Quarter3. My aim is to find sum of Total hours of  "Total hours(Q3)""Total Hours(Q1)" "Total Hours(Q2)" as Totalhours . I tried preceding load but gave error. Please suggest.

 

Load*,
sum("Total hours(Q3)")+sum("Total Hours(Q2)") + sum("Total Hours(Q1)") as Totalhours;

[ Quarter3]:

LOAD
Country,
"Full Name",
Position,
SubCategory,
"Task Name",
Quarter,
"2019/07",
"2019/08",
"Total hours(Q3)" ,

FROM [lib://AttachedFiles/All Tasks Data Q3 .xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate

 

[QuarterQ2]:

LOAD
Country ,
"Full Name",
Position,
SubCategory,
"Task Name",
"2019/04" ,
"2019/05" ,
"2019/06" ,
"Total Hours(Q2)",
Quarter,
FROM [lib://AttachedFiles/All Task Data-Q2.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate
[QuarterQ1]:

LOAD
Country ,
"Full Name",
Position,
"Sub Area",
"Task Name",
"2019/01" ,
"2019/02" ,
"2019/03" ,
"Total Hours(Q1)"
Quarter,

FROM [lib://AttachedFiles/All Task data-Q1.xlsx]
(ooxml, embedded labels, table is Sheet1);

Region_country:
LOAD
Region,
Country
FROM [lib://AttachedFiles/Country to Region.xlsx]
(ooxml, embedded labels, table is Sheet1);

Thanks,

Niha

1 Solution

Accepted Solutions
niha
Creator II
Creator II
Author

I,

Finally I came to a conclusion to do set analysis individually. 

=(sum({<FM ={'MSL'}, [Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q1hours)+sum({< FM ={'MSL'},
[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q2hours)+ sum({<FM ={'MSL'},[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q3hours))
/
(sum({<FM ={'MSL'}>}Q1hours)+sum({< FM ={'MSL'}>}Q2hours)+sum({<FM ={'MSL'}>}Q3hours))

Thanks,

Niha

 

View solution in original post

6 Replies
tchovanec
Creator II
Creator II

It looks like you are missing the group by clause

JGMDataAnalysis
Creator III
Creator III

Test:
NOCONCATENATE
LOAD
    Country,
    "Full Name",
    Position,
    "Sub Area",
    "Task Name",
    "2019/01",
    "2019/02",
    "2019/03",
    "Total Hours(Q1)" AS TotalHours,
    Quarter
FROM [lib://AttachedFiles/All Task data-Q1.xlsx]
(ooxml, embedded labels, table is Sheet1)
;
CONCATENATE (Test)
LOAD
    Country,
    "Full Name",
    Position,
    SubCategory,
    "Task Name",
    "2019/04",
    "2019/05",
    "2019/06",
    "Total Hours(Q2)" AS TotalHours,
    Quarter
FROM [lib://AttachedFiles/All Task Data-Q2.xlsx]
(ooxml, embedded labels, table is Sheet1)
;
CONCATENATE (Test)
LOAD
    Country,
    "Full Name",
    Position,
    SubCategory,
    "Task Name",
    "2019/07",
    "2019/08",
    "Total hours(Q3)" AS TotalHours,
    Quarter
FROM [lib://AttachedFiles/All Tasks Data Q3 .xlsx]
(ooxml, embedded labels, table is Sheet2);

Region_country:
NOCONCATENATE
LOAD Region, Country
FROM [lib://AttachedFiles/Country to Region.xlsx]
(ooxml, embedded labels, table is Sheet1);
niha
Creator II
Creator II
Author

Thanks. 

"Total Hours(Q1)" AS TotalHours,should be TotalhoursQ1, "Total Hours(Q2)" AS TotalHours, should be TotalhourQ2, "Total Hours(Q3)" AS TotalHours, should be TotalhoursQ3 as all have different values . It is the average of each quarter. My aim is to find Total year hours for which I need to sum all three quarters. Now I did preceding load on each table and concatenate it which is working fine. My aim is to get Total hours of all three quarter in script. I want to get TotalYrhours. when I get preceding load above [AllTask Quarter3] I am getting error Q2hours not found.

/Load*,
//sum("Q3hours")+sum("Q2hours") + sum("Q1ours") as TotalYrhours;

 


[AllTask Quarter3]:
LOAD
Country,
"Full Name",
"Task Name",
Quarter,
"2019/07",
"2019/08",
"Total hours(Q3)" as Q3hours,
FM,
"Therapeutic Area";

LOAD
Country,
"Full Name",
"Task Name",
Quarter,
"2019/07",
"2019/08",
"Total hours(Q3)" ,
FM,
"Therapeutic Area"
FROM [lib://AttachedFiles/All Tasks Data Q3 .xlsx]
(ooxml, embedded labels, table is Sheet2);

Concatenate
LOAD
Country ,
"Full Name",
"Task Name",
"2019/04" ,
"2019/05" ,
"2019/06" ,
"Total Hours(Q2)" as Q2hours,
Quarter,
FM,
"Therapeutic Area";


[AllTask QuarterQ2]:

LOAD
Country ,
"Full Name",
"Task Name",
"2019/04" ,
"2019/05" ,
"2019/06" ,
"Total Hours(Q2)",
Quarter,
FM,
"Therapeutic Area"

FROM [lib://AttachedFiles/All Task Data-Q2.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate
[AllTask QuarterQ1]:
LOAD
Country ,
"Full Name",
"Task Name",
"2019/01" ,
"2019/02" ,
"2019/03" ,
"Total Hours(Q1)" as Q1hours,
Quarter,
FM,
"Therapeutic Area";

LOAD
Country ,
"Full Name",
Position,
"Task Name",
"2019/01" ,
"2019/02" ,
"2019/03" ,
"Total Hours(Q1)",
Quarter,
FM,
"Therapeutic Area"
FROM [lib://AttachedFiles/All Task data-Q1.xlsx]
(ooxml, embedded labels, table is Sheet1);

 

Region_country:
LOAD
Region,
Country
FROM [lib://AttachedFiles/Country to Region.xlsx]
(ooxml, embedded labels, table is Sheet1);

Thnaks,

Niha

JGMDataAnalysis
Creator III
Creator III

Using the structure of the script that I gave you the only thing you would have to do is at the FrontEnd level is to use this measure: Sum (TotalHours).

/Load*,
//sum("Q3hours")+sum("Q2hours") + sum("Q1ours") as TotalYrhours;

[AllTask Quarter3]:

...

This makes no sense. When using the Sum function in this context you must use a GROUP BY clause. In addition, with the * you are including all the fields in the table which you should pass to GROUP BY.
What is the purpose of calculating the total in script?

niha
Creator II
Creator II
Author

Hello,

Thanks. The purpose is:

1.  To see total hours isn each quarter which I can do by SUM ( Totalhour (Q1)) so on....

2. I also want to know Total hours of all Quarter say  Yearhours . It is because I have to do some advance set analysis.

lets see below examples. This gives me percentage of hours for a particular task and field for Quarter3. This is the measure. Dimension is region.

Sum({<Field= {ML}, [Task Name] = {[interaction]}>}[Total hours(Q3)])/Sum({<Field= {ML}>}[Total hours(Q3)]) 

Now instead of Q3 hours I want to do it for all year which is SUM of all Quarter hours. I can then take Quarter in my filterpane and can see for all quarter. I am expecting a Barchart with region as dimension and measure as above but instead of Q3 quarter I want whole year. so I want to replace Q3 with Q1+Q2+Q3.

hope this helps.

Regards,

Niha

 

niha
Creator II
Creator II
Author

I,

Finally I came to a conclusion to do set analysis individually. 

=(sum({<FM ={'MSL'}, [Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q1hours)+sum({< FM ={'MSL'},
[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q2hours)+ sum({<FM ={'MSL'},[Task Name] = {'EExternal SL Engagment - SL interaction'}>}Q3hours))
/
(sum({<FM ={'MSL'}>}Q1hours)+sum({< FM ={'MSL'}>}Q2hours)+sum({<FM ={'MSL'}>}Q3hours))

Thanks,

Niha