topic Re: Create flag in load script in QlikView

Create flag in load script

Anonymous — Sun, 03 May 2015 18:26:01 GMT

Good day!

in load script i load two fileds from xlsx file:

Distributors:

LOAD

Distributor,

Name

FROM

$(DataPath)Test.xlsx

(ooxml, embedded labels, table is Лист1);

I need to create flag in script, which will write 1 if Distr have more than 1 Name

Neccesarry result in attached picture

Thank you!

Re: Create flag in load script

petter — Sun, 03 May 2015 18:43:37 GMT

Distributors:

LOAD

Distributor,

If( Exists(Name) , 1 , 0 ) AS Flag,

Name

FROM

$(DataPath)Test.xlsx

(ooxml, embedded labels, table is Лист1);

But this will only flag the second, third or later occurence ... not the first.

So you should do a resident load of the distributors to get a list of those with more than one name.

Or you will have to reprocess the entire Distributors table to update all rows for distributors with more than one name.

Re: Create flag in load script

Anonymous — Sun, 03 May 2015 18:47:55 GMT

Thanks.

Could you please show how to do resident load in my example?

Re: Create flag in load script

stigchel — Sun, 03 May 2015 19:24:31 GMT

Try with this, it will flag both

Data:

LOAD Distributor&Name as Key,

Distributor,

Name

FROM

C:\Users\Piet.Hein.Stigchel\Downloads\test.xlsx

(ooxml, embedded labels, table is Лист1);

Join load Key,1 as Flag Where Cnt>1;

Load Distributor&Name as Key,

Count(Name) as Cnt

Resident Data Group by Distributor&Name;

Re: Create flag in load script

MarcoWedel — Sun, 03 May 2015 19:51:44 GMT

‌Left Join (Distributors)

LOAD Distributor,

Sign(Count(Distinct Name)) als MultNameFlag

Resident Distributors

Group By Distributor;

Hope this helps

regards

Marco

Re: Create flag in load script

stigchel — Sun, 03 May 2015 20:00:24 GMT

Actually to make it clearer (I started differently) see below code. To explain it uses a so called preceding load on top of a resident load of the original data. Resident loads tables previously loaded, the preceding load is like a piped load that uses the result from the inner load

Data:

LOAD Distributor&Name as Key,

Distributor,

Name

FROM

C:\Users\Piet.Hein.Stigchel\Downloads\test.xlsx

(ooxml, embedded labels, table is Лист1);

Join load Key,1 as Flag Where Cnt>1;

Load Key,

Count(Name) as Cnt

Resident Data Group by Key;

Re: Create flag in load script

stigchel — Sun, 03 May 2015 20:11:35 GMT

I think that will flag Distr2 and Distr3 as well Marco, since the count for that is 1.

Re: Create flag in load script

MarcoWedel — Sun, 03 May 2015 22:44:23 GMT

Hi,

good point, thanks.

Maybe this solution works better:

Distributors:
LOAD Distributor, Name
FROM [https://community.qlik.com/servlet/JiveServlet/download/766458-162231/test.xlsx] (ooxml, embedded labels, table is Лист1);

Left Join (Distributors)
LOAD Distributor,
    Sign(Count(Name)-1) as MultNameFlag
Resident Distributors
Group By Distributor;

hope this helps

regards

Marco

Re: Create flag in load script

stigchel — Mon, 04 May 2015 09:51:50 GMT

I'm guessing that you andreyfcdk91 will use Marco's answer as a count of flag where it has to be >1 for multiple names. That's fine, but others looking for answers to a similar problem might be puzzled as to why that answer is correct, as it does not meet your original requirements.

Furthermore, the following script would do the same in a simpler way. As it is a resident load, I think the the sign of Count will always be 1, unless Distributor is Null.

Distributors:

LOAD

Distributor,

Name,

if(Len(Trim(Distributor))>0,1) as Flag

FROM

C:\Users\Piet.Hein.Stigchel\Downloads\test.xlsx

(ooxml, embedded labels, table is Лист1);