topic Re: Dynamic assignment in load script in QlikView

Dynamic assignment in load script

Anonymous — Thu, 11 Aug 2016 12:27:18 GMT

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

Re: Dynamic assignment in load script

vishsaggi — Thu, 11 Aug 2016 14:40:27 GMT

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 ?

Re: Dynamic assignment in load script

vishsaggi — Thu, 11 Aug 2016 14:45:32 GMT

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

Re: Dynamic assignment in load script

maxgro — Thu, 11 Aug 2016 14:58:11 GMT

Check_Table:

load * inline [

Order

];

Main_Table:

load

rowno() as Order,

'another field' as field1

AutoGenerate 20;

Left Join (Main_Table)

LOAD

Order,

1 as FlagAssigned

Resident Check_Table;

Re: Dynamic assignment in load script

Anonymous — Thu, 11 Aug 2016 15:24:03 GMT

Thanks for this Vish, worked perfectly!

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

Regards,

Keith

Re: Dynamic assignment in load script

Anonymous — Thu, 11 Aug 2016 15:25:24 GMT

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

Re: Dynamic assignment in load script

maxgro — Thu, 11 Aug 2016 15:34:42 GMT

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