How to retain and compare values of a column from a table which is reloaded periodically?

[Anonymous]

Hello Everyone, I m fairly new to Qlik Sense. I'd like your advise on the following:

I am working with a table that is something similar to this:

Supplier Name	Supplier Score
Supplier 1	30
Supplier 2	79
Supplier 3	55

This table is from an excel file. This excel file is updated weekly and is reloaded in Qlik Sense. What I want to do is, every time the user reloads the data, I want to store the "Supplier Score" in a different table by renaming it as "<Date>"

Essentially this new table will be a log of how the supplier score changes over time. It should look like this:

Supplier Name	10/22/2018	10/29/2018	11/5/2018	11/12/2018
Supplier 1	30	45	55	10
Supplier 2	79	80	80	50
Supplier 3	55	63	44	12

This is the place I need help. First of all, I need to know how to construct a table like this, and secondly, I need to maintain just 4 weeks of data in this table. So when the data is loaded on 19th November 2018, the column of 22nd October 2018 is discarded and  the new column is added.

I can do this in Python and then just feed the table to qlik sense. But, it would be easier for the user if this happened in qlik itself.

After this table is constructed, I am using the cross table function to un-pivot this table and feed it into my line chart visualization.

Thank you for your help!

[lironbaram]

hi

why not build your table as you need it from the start

so when you load the data you just add a date field,

your basic scirpt looks like this :

Data:

load  [Supplier Name],

          [Supplier Score],

          today() as Date

from SourceTable;

///////////////loading the previous 3 dates from the Data qvd//////////

Load *

From Data.qvd (qvd)

where Date >=date(today()-21);

Store Data into Data.qvd;

[CathyRDuvall]

Liron,

Your idea will  work but need to replace in the load statement;

today() as Date to

Supplied Date  (manually added the report date along with the other information from the suppliers)

So that each entry is dated separately, then the where Date >= date(today()-21) will show only dates from previous 21 days.

[Anonymous]

Hello Liron,

Thanks. This works.

But I have a question regarding the second part of your solution.

What if the data load is done manually and is not exactly a week apart? in this case I would want the 3 most recent columns. Is there a way to do that?

Also, could I assign the date to a variable and load the supplier score as follows:

vDate = today()

LOAD [Supplier Name], [Supplier Score] AS vDate

FROM <data source>

I ask because I will be running the crosstable() function after to get the following table:

Supplier Name	Date	Score
Supplier 1	10/22/2018	30
Supplier 1	10/29/2018	45
Supplier 1	11/05/2018	55