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

Join two IF function fields

Hi,

In the Qlikview script I have two IF functions in the same table.

   

KOB1:
QUALIFY *;

LOAD

Kostensoort,
IF(Kostensoort<6040000,'Materiaal',IF(Kostensoort<7000000,'Uitbesteding',IF(Kostensoort<9000000,'Netto gefactureerd aan derden',IF(Kostensoort<9999999,'Uren','Niet toegekend'))))as A,
Leverancier,

If(Match(Leverancier, 'Cofely Experts BV','Laudy Bouw & Planontwikkeling bv')>0,Leverancier, 'Leverancier contract') as LeverancierSamen

I'd like to create a new field  'A' + 'LeverancierSamen'

Example:

Kostensoort; A ; LeverancierSamen; NewField

6000001; Materiaal ; Supplier 15 ; Materiaal Supplier 15

6040001; Uitbesteding ; Supplier 10 ; Uitbesteding Supplier

6040021; Uitbesteding ; Supplier 33 ; Uitbesteding Supplier 33

Grtz,

Ralph

1 Solution

Accepted Solutions
Not applicable

Re: Join two IF function fields

Hi,

I would use the technique of preceding load.

TableA:

LOAD A,

       Leverencier,

     A + Leverencier as B;

Your LOAD statement FROM the source;

There are 2 LOAD at the row. The really loaded is the first one (there is no FROM xxx). The second  LOAD "creates a temp table" from the source.

Fabrice

8 Replies
Not applicable

Re: Join two IF function fields

Hi Ralph,

You can create a variable which could be Field1 & Field 2 that will append both of these

Thanks,

Sai

Not applicable

Re: Join two IF function fields

Hi,

I would use the technique of preceding load.

TableA:

LOAD A,

       Leverencier,

     A + Leverencier as B;

Your LOAD statement FROM the source;

There are 2 LOAD at the row. The really loaded is the first one (there is no FROM xxx). The second  LOAD "creates a temp table" from the source.

Fabrice

Not applicable

Re: Join two IF function fields

Hi,

try this,

KOB1:

QUALIFY *;

LOAD

Kostensoort,
IF(Kostensoort<6040000,'Materiaal',IF(Kostensoort<7000000,'Uitbesteding',IF(Kostensoort<9000000,'Netto gefactureerd aan derden',IF(Kostensoort<9999999,'Uren','Niet toegekend'))))as A,
Leverancier,

If(Match(Leverancier, 'Cofely Experts BV','Laudy Bouw & Planontwikkeling bv')>0,Leverancier, 'Leverancier contract') asLeverancierSamen

KOB2:

LOAD

Kostensoort,

A,

LeverancierSamen,

A &'-'& LeverancierSamen as NewField,

Resident KOB1;


Drop Table KOB1;

Hope it helps,

Regards,

Ravikant

Not applicable

Re: Join two IF function fields

Hello Aunez,

Will you help me with my "LOAD statement FROM the source".

The script:

 

KOB1:
QUALIFY *;

LOAD Kostensoort,
IF(Kostensoort<6040000,'Materiaal',IF(Kostensoort<7000000,'Uitbesteding',IF(Kostensoort<9000000,'Netto gefactureerd aan derden',IF(Kostensoort<9999999,'Uren','Niet toegekend'))))as A,
Kostensoortomschr.,
Inkoopdocument,
Leverancier,
If(Match(Leverancier, 'Cofely Experts BV','Laudy Bouw & Planontwikkeling bv')>0,
Leverancier, 'Leverancier contract') as LeverancierSamen,
Besteltekst,
[Totl. ingev. hoevh.],
[Geboekte hvh.eenh.],
[Partner prest.soort],
[Waarde/CO-valuta],
Personeelsnummer,
[Naam van werknemer of sollicitant],
[Hoeveelheid totaal],
Hoeveelheidseenheid,
Documentdatum,
Month(Documentdatum) as Maand,
Week(Documentdatum) as Week,
Objectomschrijving,
EC_Order
FROM
Data\SAPDumpFinancieel.xlsx
(
ooxml, embedded labels, table is KOB1);
UNQUALIFY *;

I tried:

 

TableA:

LOAD

KOB1.A,
KOB1.LeverancierSamen,
KOB1.A + KOB1.LeverancierSamen as B
FROM KOB1;

Thanks a lot,

Ralph

Not applicable

Re: Join two IF function fields

hi

try thi hope this will help

KOB1:
QUALIFY *;

LOAD

Kostensoort,
IF(Kostensoort<6040000,'Materiaal',IF(Kostensoort<7000000,'Uitbesteding',IF(Kostensoort<9000000,'Netto gefactureerd aan derden',IF(Kostensoort<9999999,'Uren','Niet toegekend'))))as A,
Leverancier,

If(Match(Leverancier, 'Cofely Experts BV','Laudy Bouw & Planontwikkeling bv')>0,Leverancier, 'Leverancier contract') as LeverancierSamen

From .......

KOBLink:

Load

8,

A&LeverancierSamen as Newfield

Resident KOB1;

Drop table KOB1;

Not applicable

Re: Join two IF function fields

Ralph,

To explain a little more about PRELOAD:

Qualify *;
Table1:
LOAD Prod,
Month,
Flag1 & '|' & Flag2 as Flag,
Units

if (Flag1 = 'A', Amount) as Amount;
LOAD Prod,
Month,
Country,
Amount,
Units,
if(Month < 3, 'A', if(Country > 2, 'B', 'C')) as Flag1,
if(Amount > 50, 'X', if(Units < 10, 'Y', 'Z')) as Flag2

FROM[101221.xlsx] (ooxml, embedded labels, table is Feuil1);

Your table will contain the following fields: Prod, Month, Flag, Units and Amount. No country.

The LOAD statement that loads the table is the first one: it has no source (no FROM, no RESIDENT). The LOAD that reads the file is the second one (but it does not populate the table)

As you see, Flag1 and Flag2 can be reused in the first LOAD.

To use all the fields of the second load, you can use the start (*) :LOAD *, if(Flag1 = .....) as Flag

Fabrice

Not applicable

Re: Join two IF function fields

Aunez,

Thanks for the quick response.

Now I get it what you mean with two loads.

Later at the day I gonna try it.

Thanks,

I let you know.


Not applicable

Re: Join two IF function fields

Good

Take care also not to use too much the QUALIFY statement. The links between the different tables are made automatically by the names, and only by the names (not like SQL). If you use "Qualify *", all the names will be different, and therefore no link in your model.

Fabrice

Community Browser