Dynamic assignment in load script

[Anonymous]

Dear All,

I want to assign a field value in a table during load, based on a value in a previously loaded table, i.e. Check_Table has a list of 20 Orders, Main_Table has all Orders, I want to mark each Order in Main_Table with a flag to identify if it is present in the Check_Table list or not.

could do a join of the two tables but this does not allow me to subsequently select Orders which are not in the Check_Table (as they will have a NULL value in the flag field and not selectable in my dashboard).

Hence, during my load script for Main_Table I want to use something along the lines of:

If [Order] in Check_Table,

     Flag assigned 'Yes'

Else

     Flag assigned 'No'

Does anyone know how I can do this in Qlikview?

Thanks in advance,

Keith

[vishsaggi]

Try this and let me know if not please share you main and check tables for a quick look at it.

Please find attached files and let me know how it works ?

[vishsaggi · Accepted solution]

Sorry an update in the script.

Use below.

CHECK:

LOAD

     ID,

     ORDERID AS CheckOrderID,

     'Yes' AS Flag

FROM

[..\Desktop\Checking.xlsx]

(ooxml, embedded labels, table is CHECK);

TEMP:

MAPPING LOAD CheckOrderID,

     Flag

Resident CHECK;

MAIN:

LOAD *,

     ApplyMap('TEMP', ORDERID, 'No') AS FlagExists;

LOAD Name,

     ORDERID

FROM

[..\Desktop\Checking.xlsx]

(ooxml, embedded labels, table is MAIN);

[maxgro]

Check_Table:

load * inline [

Order

1

2

3

4

];

Main_Table:

load

  rowno() as Order,

  'another field' as field1

AutoGenerate 20;

Left Join (Main_Table)

LOAD

  Order,

  1 as FlagAssigned

Resident Check_Table;

[Anonymous]

Thanks for this Vish, worked perfectly!

I hadn't used ApplyMap much previously, might use it more in the future now...

Regards,

Keith

[Anonymous]

Hi Massimo,

Really appreciate the response, thanks for taking the time.

Once I got my head around ApplyMap though I found it easier to use Vish's approach this time.

Thanks,

Keith

[maxgro]

Me too I prefer the applymap solution. My answer was just to show a different solution.