topic Re: Problem with peek and table.fieldname in App Development

Problem with peek and table.fieldname

curiousfellow — Fri, 03 Feb 2023 13:06:55 GMT

I have a lot of scripts using the peek function, and they work as expected.

At this moment however I have problems with the function and I can not explain why. When using next script the results in table FirstEmployee I get the correct value in EmpCode.
In table TryMySelf the value of Empcode2 remains empty. I tried various options, with and without rowno and tablename as second and third parameter in the peek function.

In other scripts I allways use the tablename to refer to the previous row , Peek('Tablename.fieldname').

What is wrong in the script creating table 'TryMySelf' ? We are using Qlikview May 2021 SR1

EmployeeDates:

Load * Inline [

EmployeeCode|StartDate|EndDate

101|02/11/2010|23/06/2012

102|01/11/2011|30/11/2013

103|02/01/2012|
103|02/01/2013|
103|02/01/2014|

104|02/01/2012|31/03/2012

105|01/04/2012|31/01/2013

106|02/11/2013|

] (delimiter is '|');

Qualify '*';
TryMySelf:
Load EmployeeCode as EmployeeCodex,
EmployeeCode as EmployeeCode2,
Peek('TryMySelf.EmployeeCodex') As EmpCode2,
StartDate,
EndDate
Resident EmployeeDates;

UNQUALIFY '*';
FirstEmployee:

Load EmployeeCode, Peek('EmployeeCode',-1) As EmpCode

Resident EmployeeDates;

Re: Problem with peek and table.fieldname

edwin — Fri, 03 Feb 2023 13:46:18 GMT

try this (looks like Qualify and peek does not play well together):

TryMySelf: Load EmployeeCode as TryMySelf.EmployeeCodex, EmployeeCode as TryMySelf.EmployeeCode2, Peek('TryMySelf.EmployeeCodex') As TryMySelf.EmpCode2, StartDate as TryMySelf.StartDate, EndDate as TryMySelf.EndDate Resident EmployeeDates;

Re: Problem with peek and table.fieldname

vinieme12 — Mon, 06 Feb 2023 06:33:40 GMT

not sure what exactly is happening here but using coalesce() with peek() on qualified and pre-qualified field works

@rwunderlich @MarcoWedel @marcus_sommer @Vegar

Qualify *;
TryMySelf:
Load EmployeeCode as EmployeeCodex,
EmployeeCode as EmployeeCode2,
coalesce(Peek('TryMySelf.EmployeeCodex'),peek('EmployeeCodex')) As EmpCode2,
StartDate,
EndDate
Resident EmployeeDates;

Re: Problem with peek and table.fieldname

Vegar — Mon, 06 Feb 2023 08:06:49 GMT

I notice that @curiousfellow are using single quotes.

Qualify '*';

Single quotes ' ' are normally interpreted as string values by Qlik.

Try not to use quotes or use double instead.

Qualify *;

Qualify "*";

I think that will solve your issue

Re: Problem with peek and table.fieldname

marcus_sommer — Mon, 06 Feb 2023 08:15:59 GMT

Without doing any tests here I could imagine that you are struggling with the order in which the explicit field-renaming and the (per qualify) implicit field-renaming are performed.

My recommendation is very simple: don't use qualifying! In my experience it's much overvalued and often applied without a real use within a BI tool like Qlik. That's more or less a "standard" within the sql-world makes it not mandatory useful within any reporting.

Qualify looks so simple and easy but it makes many things within the data-model development much more complex - not only by the interrecord-functions else also by mapping/joining and even by storing/dropping any content. Further and more important it prevents often the right look on the data and keeping the people struggling with multiple fact-tables instead of merging them into a single table.

If it's necessary to keep the source-information it could be often done with an appropriate source-field within the data and if any renaming on a few fields is necessary just to do it explicitly (which is always best practice).

Re: Problem with peek and table.fieldname

curiousfellow — Tue, 07 Feb 2023 07:40:38 GMT

The first thing I learned on a Qlik course was "Use Qualify" :)

Also, in my data source I have 84 tables with the same column name. Many other field names also occur several times in the tables. Renaming the fields with the same column name takes too much time.

Of course I don't load all the tables and fields but this is what works best for me.

In this case, I now need to create a temporary table.
Thank you for your response.

Re: Problem with peek and table.fieldname

marcus_sommer — Tue, 07 Feb 2023 08:21:08 GMT

The training was just very basic with very simple examples. Of course they will say it's applicable to the most and more complex scenarios - but it's not true. As far as goes above you will need more knowledge and often other approaches because the provided simple solutions are rather the opposite to a professional work. That's now not specially for Qlik else the marketing and (pre-)sales guys of all tools do the same by creating the impression the tools could everything from alone respectively the wizards will automatically do all the magic ...

84 tables with (mostly) the same columns - the only sensible way of connecting them will be to concatenate them into a single table by harmonizing at most as possible field-names and data-structures.

Because if not it becomes quite difficult (whereby not impossible) to associate them to each other and to further tables without creating a bunch of synthetic keys and/or circular tables. After this it won't be easier because now you have dozens of fields with tableX.date, tableY.date, tableZ.date and so on. Do the table-prefixes really helping to understand which one comes from where and contained what beyond that to access them in dimensions and expressions and synchronize them and ... IMO it goes definitely in the wrong direction.

Re: Problem with peek and table.fieldname

curiousfellow — Wed, 08 Feb 2023 10:35:51 GMT

I am afraid I have not been clear about the 84 tables. What I meant was that some of the fieldnames in those tables are the same name, adress or city for example. The tables contain other fields too.

Re: Problem with peek and table.fieldname

marcus_sommer — Wed, 08 Feb 2023 11:42:40 GMT

To get asynchron tables because not all concatenated tables have the same data-structures are not a technically issue and will often work smoothly. Of course it will depend ... by only 3 common columns and each 3 dozens different ones it may not be the most suitable solution but it's reverse and just a few columns are different it's a very sensible data-model.

The essential point is to reduce the number of tables (not only per concatenating else also per join + mapping) ideally in the direction of a star-scheme (a single fact-table with n dimension-tables) because it avoids all the challenges with synthetic keys and circular loops without qualifying everything, is easy to develop and to maintain, simplified the dimension-access + selections and the creation of rather simple expressions - and it performs very well. Therefore it's the officially recommendation of a data-model as best compromise in regard to efforts and results.

Regardless to each kind of data-model is that you to know the data - what is what + from where + how is the data-quality + any missing data and so on? If anything needs to be done - cleaning + preparing + filling/matching + populating + what ever on the data - it must be done so or so ...