Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

An interesting load question

I'm trying to load a file where the metrics are grouped by the first column, grouped as showen below

So for example, I sold Fords on 10/1-10/5. How can my QV Load regonized 10/2/-10/5 as belonging to Ford?

My result show look like the following

Tags (3)
1 Solution

Accepted Solutions
Not applicable

An interesting load question

Daniel, peek() will do what you need. The reason is that peek() pulls from the current associative DB table, while previous() pulls from the data source. This is a subtle difference, and in some cases peek works where previous does not or vice versa. So in your case (assuming the data is in correct order), you can read the first record with an account, then the second record looks at the first and picks up the account, then the third record reads the second record and so on. So you are always only looking back one record at the data that has already been loaded and updated.

Something like:

IF(isnull(Account), peek('Account'), Account) as Account

Tom

19 Replies
Not applicable

An interesting load question

Hi Jonathan,

if it is an excel file than:

go to table viewer, select your table, qlik next until you see "Enable Transformation Step" => "FILL" => Fill Cell.

and define your rule.

Good luck!

Rainer

danielblank
New Contributor III

An interesting load question

Hi Rainer,

what if it is not an Excel file? I have this case...

Best regards,

Daniel

Not applicable

An interesting load question

You should use the Previous function IF(isnull(Field),previous(Model),Model) as Model

But in this case you need to add an Order by in your load statement.

Sébastien

An interesting load question

I'd better use "Rotate" / "Transpose" in the "Enable Transformation Step" (QV v9.00 SR1)

danielblank
New Contributor III

An interesting load question

Well, I´m araid, it is not an Excel file.

I get account data in the format

Account CounterAccount
5400 2200
2300
4300
5600 2100
3600
4200
1300
4900

Now I need to fill the empty values in the field Account. The previous() or peek() function does only work if the previous record has a value for Account and I don´t know how many records back I have to look. Every account can have many CounterAccounts... The number is not fix.

Any idea?

Kind regards,

Daniel

Not applicable

An interesting load question

Maybe with a kind of loop on the table...

For each value if Model is null then use variable else LET variable = Model...

Sébastien

Not applicable

An interesting load question

Daniel, peek() will do what you need. The reason is that peek() pulls from the current associative DB table, while previous() pulls from the data source. This is a subtle difference, and in some cases peek works where previous does not or vice versa. So in your case (assuming the data is in correct order), you can read the first record with an account, then the second record looks at the first and picks up the account, then the third record reads the second record and so on. So you are always only looking back one record at the data that has already been loaded and updated.

Something like:

IF(isnull(Account), peek('Account'), Account) as Account

Tom

MVP
MVP

An interesting load question

I was about to say the same. I couldn't guess why peek() wouldn't work, so I built myself an example, and it worked just fine, as I'd have expected. I'd avoid IsNull() because of issues with it on 64-bit, and you don't need the single quotes. I honestly thought the single quotes would prevent it from working, but they apparently don't. Anyway, the example I'd built uses this script (in the real world, make sure your data is sorted properly):

LOAD
if(len(Account),Account,peek(Account)) as Account
,CounterAccount
INLINE [
Account,CounterAccount
5400,2200
,2300
,4300
5600,2100
,3600
,4200
,1300
,4900
];

Not applicable

An interesting load question

The full syntax is with the single quotes, but as you've observed it seems to work without them as well.

Community Browser