An interesting load question

[#1]

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

[#2]

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]

Hi Rainer,

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

Best regards,

Daniel

[#4]

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

[Miguel_Angel_Baeyens]

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

[danielblank]

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

[#7]

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

[#8 · Accepted solution]

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

[johnw]

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
];

[#10]

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