Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have data that follows the format of
Name, Date, Key, Data
A, 11/10/2012, 0, 100
A, 01/28/2010, 0, 20
A, 01/28/2013, 1, 30
B, 01/28/2013, 0, 33
I want to load the data so that, per each name if it has Key = 0, and is in last 12 months then sum of data should be a new field. So .. it should be like this .....
Name, Total Data
A, 130
B, 33
I can do it in set analysis, but I would like to do it load script, just create a new table, then have to just load that new table in tablebox. Anyway, I was hoping that you could direct me to right path, or show me how to accomplish this task.
Thank you for your time.
If your current data is in a table named "Data"
then you could use a script like this
Totals:
load
Name,
sum(Data) as Total
resident Data
where Key = 0
and Date >= addmonths(today(), -12)
group by Name ;
This will filter the data to select only records with Key = 0 and are in the last 12 months, and then sum the Data values by Name.
This will work, though normally it is better to keep the data at a granular level in QlikView and do the summing in your charts as this gives you the most flexibility on how to view and analyse the data.
Try something like this:
[Aggr.Table]
LOAD
Name AS [Aggr.Name],
Sum(Data) AS [Aggr.Total Data]
RESIDENT [OriginalTable]
WHERE (Key = 0) AND (Date >= AddMonths(Today(), -12))
GROUP BY Name;
If your current data is in a table named "Data"
then you could use a script like this
Totals:
load
Name,
sum(Data) as Total
resident Data
where Key = 0
and Date >= addmonths(today(), -12)
group by Name ;
This will filter the data to select only records with Key = 0 and are in the last 12 months, and then sum the Data values by Name.
This will work, though normally it is better to keep the data at a granular level in QlikView and do the summing in your charts as this gives you the most flexibility on how to view and analyse the data.
What would happen if there is no date or Key for that account, would I get nil value ?
Thanks btw ❤️
If there are records with no date value then these would not be included in the Totals table. You could change the load script to replace any blank dates with a default value possibly. Similarly if the Key field is blank then those rows would also be excluded from the Totals table due to the where clause.