topic Re: Peek function - Populate missing data in QlikView

Peek function - Populate missing data

Wed, 15 Apr 2015 20:10:01 GMT

Hi the community,

After reading a few discussion on the forum, I kind of found what I needed but not exactly.

I have 2 qvd files and one common variable between the 2 files.

I used the join function to obtain only one unique qvd.

What I want to do now is to populate the missing data from the second file.

See as below

Data_TEST:

LOAD

[Period ID],
REPORT_Code,
[TEST DOCUMENT NUMBER],
[TEST REFERENCE],
[TEST AMOUNT],
[TEST TYPE]
FROM
$(RootDir_FACT)monthlysummary_TEST*.qvd (qvd);

Outer Join (Data_TEST)

LOAD

[TEST REFERENCE],

[TEST CREATION DATE]

FROM
$(RootDir_FACT)dailyworkflow_TEST*.qvd (qvd);

STORE Data_TEST into $(RootDir_QLIKMART)TEST_volume_full_data.qvd;

As you can see, the field TEST REFERENCE is common to the 2 files.

I'm trying to get the REPORT_Code field filled up for the second file.

I used the following function, but PEEK only populate the empty cells using the above records.

// if(isnull(REPORT_Code) AND [TEST REFERENCE]=peek([TEST REFERENCE]), peek(REPORT_Code), REPORT_Code) as REPORT_Code

Furthermore, I'm trying to get to do all of this before I store both loaded data into one QVD file, so I won't have to create 2 files.

Your help would be greatly appreciated

Re: Peek function - Populate missing data

sunny_talwar — Wed, 15 Apr 2015 20:34:39 GMT

What exactly is the problem you are facing right now? You might need to use the order by statement to make sure that the correct Report_Code is getting copied down.

Best,

Sunny

Re: Peek function - Populate missing data

b_garside — Wed, 15 Apr 2015 20:40:24 GMT

You may need to show a small sample data-set that represents the problem your having.

Then the community will be able to help you easier. Or even better upload a sample QVW if possible.

Re: Peek function - Populate missing data

maxgro — Wed, 15 Apr 2015 20:50:25 GMT

in this example, all the nulls are replaced using peek, it depends on the order by

load f1, if(len(trim(f2))=0, null(), f2) as f2, rowno() as id inline [

f1,f2

1,a

1,b

2,a

2,b

2,c

2,d

];

z2: NoConcatenate

LOAD id, f1, if(isnull(f2) and Peek(f1)=f1, Peek(f2), f2) as f2

Resident z

order by f1, f2 desc;

DROP Table z;

Re: Peek function - Populate missing data

Wed, 15 Apr 2015 22:27:26 GMT

Thanks for your answers,

Besides the pbm regarding the peek function, I'm actually having trouble trouble executing what I want within the script before I store the two qvd into one. Let me re explain since I was not clear.

1) I load 2 differents QVD one after the other. Let's call them X and Y (see above)

2) The first QVD X contains my report_code but not the other. But they both have the test_reference field in common.

3) Before I store these 2 QVD into one QVD Z, I'm trying to use the peek function to populate the missing data within QVD Y.

4) When I reload QVD Y doesnt find the field report_code because those aren't joined yet.

5) I know I could do this in 2 steps, using the peek function in the unique QVD Z, but I need to do this in one step only. As new to qlikview, I'm having trouble understanding the concepts of "resident table", "temp table" etc

Sorry if I was not clear at the first time.

Re: Peek function - Populate missing data

maxgro — Wed, 15 Apr 2015 23:00:03 GMT

if you want to do an outer join between x and y, after that you could have some null report_code (from y qvd)

to replace null report_code you have to resident load the result of the outer join

2 steps; you can't outer join and replace null report_code at the same time

As new to qlikview, I'm having trouble understanding the concepts of "resident table", "temp table" etc

resident table is a table you have already loaded in memory; resident because when you read it you need a resident load (load * resident table)

LOAD data into QlikView