topic Re: Apply one specific value to all rows in QlikView

Apply one specific value to all rows

Fri, 26 Jun 2015 09:59:10 GMT

I need to add a single value for each and every row (EOD_ADJ). But when I try to join all I get is a bunch synthetic keys.

Any ideas how to do it?

LOAD Distinct
      [INSERTION DATE],
     TIME,
     [FLOW TYPE],
     AMOUNT,
     date([FLOW EFFECTIVE DATE]) as [FLOW EFFECTIVE DATE],
     date([FLOW MATURITY DATE]) as [FLOW MATURITY DATE],
     Date([FLOW EFFECTIVE DATE] + IterNo() -1 ) as DATE,
     TEXT
FROM
[Log 2.1.xlsm]
(ooxml, embedded labels, table is TRANSACTIONS)
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];

Right join LOAD
     I AS EOD_ADJ
FROM
[Log 2.1.xlsm]
(ooxml, no labels, table is [Holidays Etc])
WHERE RecNo() > 1 AND RecNo() < 3;

Re: Apply one specific value to all rows

alexandros17 — Fri, 26 Jun 2015 10:00:52 GMT

Try with a Left Join and let me know

Re: Apply one specific value to all rows

Fri, 26 Jun 2015 10:41:02 GMT

When trying left join, same result occurs, from what I can see..

Re: Apply one specific value to all rows

alexandros17 — Fri, 26 Jun 2015 10:42:57 GMT

There is a very simple way:

'I' AS EOD_ADJ

Re: Apply one specific value to all rows

Fri, 26 Jun 2015 10:45:12 GMT

Thanks Alessandro

'I' AS EOD_ADJ is loaded from another sheet and it is a single cell, not a table.

So Im not sure how you mean

Re: Apply one specific value to all rows

swuehl — Fri, 26 Jun 2015 10:49:39 GMT

You don't get any synth keys without the last join?

Could you post a screenshot of you data model and your script or document log?

Re: Apply one specific value to all rows

Fri, 26 Jun 2015 10:49:49 GMT

As I can see from where condition, it's a single value so you can store it in variable and then add with table.

Re: Apply one specific value to all rows

alexandros17 — Fri, 26 Jun 2015 10:51:46 GMT

But the value for I is Always the same, isn't it?

It is a unique value, a constant?

Re: Apply one specific value to all rows

Fri, 26 Jun 2015 10:53:22 GMT

No It changes every day unfortunately

Re: Apply one specific value to all rows

alexandros17 — Fri, 26 Jun 2015 10:58:15 GMT

Ok but when you try to add the new column to the table the value is only one?

Re: Apply one specific value to all rows

alexandros17 — Fri, 26 Jun 2015 11:01:59 GMT

TMP:

Right join LOAD
I AS EOD_ADJ
FROM
[Log 2.1.xlsm]
(ooxml, no labels, table is [Holidays Etc])
WHERE RecNo() > 1 AND RecNo() < 3;

Let myVar = Peek('EOD_ADJ', 0, 'TMP');

$(myVar) as EOD_ADJ

FROM
[Log 2.1.xlsm]
(ooxml, embedded labels, table is TRANSACTIONS)
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];

Re: Apply one specific value to all rows

Fri, 26 Jun 2015 11:17:25 GMT

I like your Idea!

Tried the below but the value of EOD_ADJ will not be stored in the variable when the table is dropped.

What am I doing wrong here?

LOAD Distinct
[INSERTION DATE],
     TIME,
     [FLOW TYPE],
     AMOUNT,
     date([FLOW EFFECTIVE DATE]) as [FLOW EFFECTIVE DATE],
     date([FLOW MATURITY DATE]) as [FLOW MATURITY DATE],
     Date([FLOW EFFECTIVE DATE] + IterNo() -1 ) as DATE,
     TEXT
FROM
Log 2.1.xlsm]
(ooxml, embedded labels, table is TRANSACTIONS)
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];

EOD_ADJUSTMENT:
LOAD
     I AS EOD_ADJ
FROM
[Log 2.1.xlsm]
(ooxml, no labels, table is [Holidays Etc])
WHERE RecNo() > 1 AND RecNo() < 3;

Set vEODADJ = EOD_ADJ;

Drop table EOD_ADJUSTMENT;

Re: Apply one specific value to all rows

senpradip007 — Fri, 26 Jun 2015 11:32:37 GMT

Try this. Hope it will help.

T2:

I AS EOD_ADJ
FROM
[Log 2.1.xlsm]
(ooxml, no labels, table is [Holidays Etc])

T1:

peek(EOD_ADJ, 0, 'T2') as EOD_ADJ
FROM
[Log 2.1.xlsm]
(ooxml, embedded labels, table is TRANSACTIONS)
While [FLOW EFFECTIVE DATE] + IterNo() -1 <= [FLOW MATURITY DATE];

Drop table T2;