Peek help! - Qlik Sense

[jag7777777]

Hi,

I'm struggling with getting data from a table using Peek. I definitely have data in my table (as I can see it in the app when I build a visualisation) but I can only get the date field when I load my table (CurrentDates) using PEEK. Here's what I started with:

YesterdaysData:

NOCONCATENATE LOAD

[TransactionType],

    [Date],

    [Branston Year Code],

    [Branston Period Code],

    [Branston Week No],

    [Branston Week Start Date],

    [Booker Year Code],

    [Booker Period Code],

    [Booker Week No],

    [Booker Week Start Date],

    [Kettleby Year Code],

    [Kettleby Period Code],

    [Kettleby Week No],

    [Kettleby Week Start Date],

    [Tesco Year Code],

    [Tesco Period Code],

    [Tesco Week No],

    [Tesco Week Start Date],

    [Waitrose Year Code],

    [Waitrose Period Code],

    [Waitrose Week No],

    [Waitrose Week Start Date]

RESIDENT [FlatDM_HMv2]

ORDER BY [Date] ASC;

CurrentDates:

LOAD

PEEK([Date], -1, 'YesterdaysData') + 1 AS TodaysDate,

            PEEK([Date], -1, 'YesterdaysData') - PEEK([Branston Week Start Date], -1, 'YesterdaysData') + 1 AS BranstonCurrentWeekDayNo,

PEEK([Branston Year Code], -1, 'YesterdaysData') AS BranstonCurrentYear,

PEEK([Branston Week No], -1, 'YesterdaysData') AS BranstonCurrentWeek,

PEEK([Branston Period Code], -1, 'YesterdaysData') AS BranstonCurrentPeriod,

            PEEK([Date], -1, 'YesterdaysData') - PEEK([Tesco Week Start Date], -1, 'YesterdaysData') + 1 AS TescoCurrentWeekDayNo,

PEEK([Tesco Year Code], -1, 'YesterdaysData') AS TescoCurrentYear,

PEEK([Tesco Week No], -1, 'YesterdaysData') AS TescoCurrentWeek,

PEEK([Tesco Period Code], -1, 'YesterdaysData') AS TescoCurrentPeriod,

            PEEK([Date], -1, 'YesterdaysData') - PEEK([Booker Week Start Date], -1, 'YesterdaysData') + 1 AS BookerCurrentWeekDayNo,

PEEK([Booker Year Code], -1, 'YesterdaysData') AS BookerCurrentYear,

PEEK([Booker Week No], -1, 'YesterdaysData') AS BookerCurrentWeek,

PEEK([Booker Period Code], -1, 'YesterdaysData') AS BookerCurrentPeriod,

            PEEK([Date], -1, 'YesterdaysData') - PEEK([Kettleby Week Start Date], -1, 'YesterdaysData') + 1 AS KettlebyCurrentWeekDayNo,

PEEK([Kettleby Year Code], -1, 'YesterdaysData') AS KettlebyCurrentYear,

PEEK([Kettleby Week No], -1, 'YesterdaysData') AS KettlebyCurrentWeek,

PEEK([Kettleby Period Code], -1, 'YesterdaysData') AS KettlebyCurrentPeriod,

            PEEK([Date], -1, 'YesterdaysData') - PEEK([Waitrose Week Start Date], -1, 'YesterdaysData') + 1 AS WaitroseCurrentWeekDayNo,

PEEK([Waitrose Year Code], -1, 'YesterdaysData') AS WaitroseCurrentYear,

PEEK([Waitrose Week No], -1, 'YesterdaysData') AS WaitroseCurrentWeek,

PEEK([Waitrose Period Code], -1, 'YesterdaysData') AS WaitroseCurrentPeriod

AUTOGENERATE(1);

This returned [Date] as a number, but NULL for everything else (even though I can see the data in the App?)

So......I I've tried to debug as best I can, but can't 'see' what's going on. I therefore introduced some variables as follows:

YesterdaysData:

NOCONCATENATE LOAD

[TransactionType],

    [Date],

    [Branston Year Code],

    [Branston Period Code],

    [Branston Week No],

    [Branston Week Start Date],

    [Booker Year Code],

    [Booker Period Code],

    [Booker Week No],

    [Booker Week Start Date],

    [Kettleby Year Code],

    [Kettleby Period Code],

    [Kettleby Week No],

    [Kettleby Week Start Date],

    [Tesco Year Code],

    [Tesco Period Code],

    [Tesco Week No],

    [Tesco Week Start Date],

    [Waitrose Year Code],

    [Waitrose Period Code],

    [Waitrose Week No],

    [Waitrose Week Start Date]

RESIDENT [FlatDM_HMv2]

ORDER BY [Date] ASC;

LET vRowCount = NoOfRows('YesterdaysData'); //get the number of rows, which are already sorted in ascending order

LET vNoRows = $(vRowCount) - 1; //row count starts at 0, so subtract 1 to get the last row no

TRACE $(vNoRows);

LET vYesterdaysDate = PEEK([Date],(vNoRows),'YesterdaysData');

TRACE $(vYesterdaysDate);

I can see that vNoRows is 215223, but the second TRACE statement returns nothing? So can't even get my date field now!

Any help would be appreciated to help understand Peek, 'seeing' content of tables / fields in debugging - and what's wrong with my script 🙂

Cheers,

[marcus_sommer]

It's not really clear for me what do you are trying to do but I assume that there are better ways to transform and/or enrich you data. Before just guessing - please provide some sample records from the yesterday-table and how they should look like after the transforming.

- Marcus

[jag7777777]

Thanks for the reply.

I'm trying to create an InCurrentWeek, InCurrentPeriod, InCurrentYear flag.

Idea is to get the dates from our main data table into a temporary table (YesterdaysData), get the last for of the table (which'll have all the last date parts) then join this to our main data table on the date field (entire script below):

//Get yeterday's dates and get them into a table, sorted by Date. We can then use this table to peek at the last record to get the 'last' date parts

//i.e. the most recent date, which should be today's date as the ETL layer runs at midnight today.

YesterdaysData:

NOCONCATENATE LOAD

[TransactionType],

    [Date] AS MaxDate,

    [Branston Year Code],

    [Branston Period Code],

    [Branston Week No],

    [Branston Week Start Date],

    [Booker Year Code],

    [Booker Period Code],

    [Booker Week No],

    [Booker Week Start Date],

    [Kettleby Year Code],

    [Kettleby Period Code],

    [Kettleby Week No],

    [Kettleby Week Start Date],

    [Tesco Year Code],

    [Tesco Period Code],

    [Tesco Week No],

    [Tesco Week Start Date],

    [Waitrose Year Code],

    [Waitrose Period Code],

    [Waitrose Week No],

    [Waitrose Week Start Date]

RESIDENT [FlatDM_HMv2]

ORDER BY [Date] ASC;

//Create a table of current dates for the 'last' date parts of the table 'YesterdaysData'.

CurrentDates:

LOAD

PEEK([Date], -1, 'YesterdaysData') + 1 AS TodaysDate,

            PEEK([Date], -1, 'YesterdaysData') - PEEK([Branston Week Start Date], -1, 'YesterdaysData') + 1 AS BranstonCurrentWeekDayNo,

PEEK([Branston Year Code], -1, 'YesterdaysData') AS BranstonCurrentYear,

PEEK([Branston Week No], -1, 'YesterdaysData') AS BranstonCurrentWeek,

PEEK([Branston Period Code], -1, 'YesterdaysData') AS BranstonCurrentPeriod,

            PEEK([Date], -1, 'YesterdaysData') - PEEK([Tesco Week Start Date], -1, 'YesterdaysData') + 1 AS TescoCurrentWeekDayNo,

PEEK([Tesco Year Code], -1, 'YesterdaysData') AS TescoCurrentYear,

PEEK([Tesco Week No], -1, 'YesterdaysData') AS TescoCurrentWeek,

PEEK([Tesco Period Code], -1, 'YesterdaysData') AS TescoCurrentPeriod,

            PEEK([Date], -1, 'YesterdaysData') - PEEK([Booker Week Start Date], -1, 'YesterdaysData') + 1 AS BookerCurrentWeekDayNo,

PEEK([Booker Year Code], -1, 'YesterdaysData') AS BookerCurrentYear,

PEEK([Booker Week No], -1, 'YesterdaysData') AS BookerCurrentWeek,

PEEK([Booker Period Code], -1, 'YesterdaysData') AS BookerCurrentPeriod,

            PEEK([Date], -1, 'YesterdaysData') - PEEK([Kettleby Week Start Date], -1, 'YesterdaysData') + 1 AS KettlebyCurrentWeekDayNo,

PEEK([Kettleby Year Code], -1, 'YesterdaysData') AS KettlebyCurrentYear,

PEEK([Kettleby Week No], -1, 'YesterdaysData') AS KettlebyCurrentWeek,

PEEK([Kettleby Period Code], -1, 'YesterdaysData') AS KettlebyCurrentPeriod,

            PEEK([Date], -1, 'YesterdaysData') - PEEK([Waitrose Week Start Date], -1, 'YesterdaysData') + 1 AS WaitroseCurrentWeekDayNo,

PEEK([Waitrose Year Code], -1, 'YesterdaysData') AS WaitroseCurrentYear,

PEEK([Waitrose Week No], -1, 'YesterdaysData') AS WaitroseCurrentWeek,

PEEK([Waitrose Period Code], -1, 'YesterdaysData') AS WaitroseCurrentPeriod

AUTOGENERATE(1);

//Add flags (InCurrentYear and InCurrentWeek) to be used in UI to sum values.

Left Join (FlatDM_HMv2)

LOAD

BranstonCurrentYear as [Branston Year Code],

1 as InCurrentBranstonYear

Resident CurrentDates;

Left Join (FlatDM_HMv2)

LOAD

BranstonCurrentYear as [Branston Year Code],

BranstonCurrentWeek as [Branston Week No],

1 as InCurrentBranstonWeek

Resident CurrentDates;

Left Join (FlatDM_HMv2)

LOAD

BranstonCurrentYear as [Branston Year Code],

BranstonCurrentPeriod as [Branston Period Code],

1 as InCurrentBranstonPeriod

Resident CurrentDates;

[marcus_sommer]

In most cases these types of flags are better generated within a dimension-table - here a master-calendar - and not within the fact-tables. Here you will find many useful informations about creating a master-calendar (and when and why it's useful to create more than one) and also how flags could be generated there: How to use - Master-Calendar and Date-Values.

- Marcus

[jag7777777]

Thanks for the reply.

We don't use a master calendar in Qlik script as we have a data warehouse in SQL, which has a time dimension table. We use a mixture of BI tools.

We blend this common time dimension with other data - which in this case is data straight from a live system. The two data sets are joined on the date field. as both data sets have this.

What I'd like to do now is resident the main data table, filter off the required date parts, sort them in ascending order - then peek the last row (which should represent the last transaction as the date join mentioned earlier is a SQL inner join). I can then Left Join these in Qlik and apply the flags.

Cheers,

[marcus_sommer]

I could just suggest to re-thing your approach which seems to me far more complicated as needed. If there is already a time dimension table you could load this into Qlik and enrich they like you needed it (if they contained date and time they should be better splitted to date and time).

Beside this I think you would need to change -1 as counter within peek to recno() because otherwise you would always call the same record whereby I doubt that I would use an outside-loop else I would probably implement the peek/previous logic within load itself.

- Marcus