topic Re: info in field in QlikView

info in field

Fri, 20 Nov 2015 15:00:57 GMT

Hi everybody,

can someone tell me how can I write in the script an info of a field in another field, but with a condition?

Example:

I Need the paydate of linetype 157, in the line with linetype <> 157. But I would load it as separate Field called "CashFlowDate"

oas_details:
LOAD

...

PAYDATE

if(LINETYPE<>157,PAYDATE (from the line with linetype = 157 (where doccode, docnum, yr and period are the same like the actual line)), PAYDATE) as CashFlowDate,

...

;
SQL SELECT

;
store oas_details into $(vDATLayerDataQVD)oas_details.qvd;

Does somebody of you have an idea?

Thanks in advance

Chris

Re: info in field

marcus_sommer — Fri, 20 Nov 2015 15:05:51 GMT

You want to access values from other then the current record? Then you could use Peek() or Previous() ?

- Marcus

Re: info in field

Fri, 20 Nov 2015 15:10:22 GMT

You can use Peek function in particular case but the usage all depends on your data input , data order and required out put.

Please post some sample data to get the best answer.

Re: info in field

Mark_Little — Fri, 20 Nov 2015 15:17:26 GMT

Hi,

If you know the line that will be stored on them Peek() or previous could give you what you need,

Another options would be to load the value in a variable. (actually will need peek to)

Table

Load

MAx(Date) as Date

From 'Your location'

Where linetype = 157;

LET vTest = PEEK('Date',-1,'Table')

Then use the variable in your if.

Mark

Re: info in field

Peter_Cammaert — Fri, 20 Nov 2015 15:31:42 GMT

If the line types form a group of records with the same key value (for example an InvoiceNo), first load your table without linetype=157. Then JOIN all linetype=157 records to the first table using the key value and call the PAYDATE you join into the first table "CashFlowDate". For example:

OAS_Details:

LOAD InvoiceNo // Or whatever key value groups linetypes

PAYDATE

RESIDENT OriginalSQLData

WHERE LineType <> 157;

LEFt JOIN(OAS_Details)

LOAD InvoiceNo, // Join on this field

PAYDATE AS CashFlowDate

RESIDENT OriginalSQLData

WHERE LineType = 157;

Best,

Peter

Re: info in field

Mon, 23 Nov 2015 07:50:16 GMT

Good morning everybody,

thanks for the answers. I dont know what exactly will help.

So maybe someone one you can help me a further. I thought the peek-solution could be the one that we need.

I made an extract of the database for one number. Every invoice has one table with dochead and one table with all the lines.

(the numbers can come again after some years, but Index is I think doccode+docnum)

I copied the table to Excel and put in column R the values that I wish to have.

I will attached the qvw-file and the qvd-file.

Thank you very much in advance.

Chris

Re: info in field

Peter_Cammaert — Mon, 23 Nov 2015 10:43:00 GMT

A quick glance at your screenshot shows that the combination of DOCNUM & DOCCODE identifies your two groups of related lines. This leads me to think that the following variation of the original code I posted should do the job. Please try it out.

OAS_Details:

LOAD DOCNUM, // Or whatever key value groups linetypes

DOCCODE,

PAYDATE

RESIDENT OriginalSQLData

WHERE LineType <> 157;

LEFt JOIN(OAS_Details)

LOAD DOCNUM, // Join on these fields

DOCCODE,

PAYDATE AS CashFlowDate

RESIDENT OriginalSQLData

WHERE LineType = 157;

Re: info in field

Mon, 23 Nov 2015 11:55:59 GMT

Hi Peter,

thank you for your help.

I have one issue. the "Resident-Line" does not work.

I am taking data our of the database and store them in a qvd-file.

Would you please look in my attachment (qvw-file)? What should I do to get it to work?

Thanks in advance.

Chris

Re: info in field

Peter_Cammaert — Mon, 23 Nov 2015 12:43:44 GMT

You should translate the example to fit your situation.

The initial LOAD-SELECT combo for table oasdocline should get an additional WHERE clause in the SELECT statement that says:

WHERE CMPCODE = 'AWD01'

//AND DOCCODE = 'RAIFR'

AND DOCNUM = ' 228613'

AND LINETYPE <> 147

;

Then LOAD-SELECT your DB Table again. Add a LEFT JOIN(oasdocline) prefix to the preceding LOAD, reduce the number of columns to just :

LEFT JOIN(oasdocline)

LOAD DOCCODE,

DOCNUM,

PAYDATE AS CashFlowDate

and change the SQL SELECT WHERE clause into something like:

WHERE CMPCODE = 'AWD01'

//AND DOCCODE = 'RAIFR'

AND DOCNUM = ' 228613'

AND LINETYPE = 147

;

after which you can STORE the internal table into a QVD file.

Or you could load the entire table from your DB into memory as a RESIDENT table, and use my previous example from there on. Works considerable faster.

Best,

Peter