topic Re: Creating a new table with dates in QlikView

Creating a new table with dates

joshrussin — Wed, 25 Nov 2020 16:16:04 GMT

I am wanting to create a table within Qlik, something like this.

With data that looks something like this.

For each area, I want it to show Processed and WIP. Any suggestions?

Re: Creating a new table with dates

aarkay29 — Wed, 19 Jul 2017 17:28:46 GMT

This might be useful

Generating Missing Data In QlikView

Re: Creating a new table with dates

manas_bn — Wed, 19 Jul 2017 19:10:40 GMT

Take a look at this: The Generic Load

Table:
//Load ID,Date,Sum(Sales) as AggrSales;
LOAD * Inline
[ID,Date,Sales
1,1/1/2017,1
1,1/1/2017,1
1,1/2/2017,1
1,1/2/2017,1
1,1/3/2017,1
2,1/1/2017,1
2,1/2/2017,1
2,1/2/2017,1];

//Sales Aggregate
AggrTable:
load ID,Date,Sum(Sales) as AggrSales Resident Table
group by ID,Date;

//Generic load ID,Date,Sales Resident Table;
drop Table Table;
Generic load ID,Date,AggrSales Resident AggrTable;

But this method will create multiple tables for each date which you may want to combine.

Not sure if there are other ways to do this.

Cheers!

Re: Creating a new table with dates

manas_bn — Wed, 19 Jul 2017 19:30:01 GMT

This piece of code worked in combining all the different generic tables into one.

FOR i = NoOfTables()-1 to 0 STEP -1
  LET vTable=TableName($(i));
  IF WildMatch('$(vTable)', 'AggrTable-1.*') THEN
    LEFT JOIN (AggrTable) LOAD * RESIDENT    [$(vTable)];
    DROP TABLE  [$(vTable)];
  ENDIF
NEXT i

But note that in the front end, you will have to figure out a way for dynamically changing columns whenever a new date is added to the data set.