Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

andreyfcdk91
New Contributor III

Create flag in load script

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!

1 Solution

Accepted Solutions

Re: Create flag in load script

‌Left Join (Distributors)

LOAD Distributor,

           Sign(Count(Distinct Name)) als MultNameFlag

Resident Distributors

Group By Distributor;

Hope this helps

regards

Marco

8 Replies
MVP
MVP

Re: Create flag in load script

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.

andreyfcdk91
New Contributor III

Re: Create flag in load script

Thanks.

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

stigchel
Honored Contributor

Re: Create flag in load script

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

‌Left Join (Distributors)

LOAD Distributor,

           Sign(Count(Distinct Name)) als MultNameFlag

Resident Distributors

Group By Distributor;

Hope this helps

regards

Marco

stigchel
Honored Contributor

Re: Create flag in load script

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;

stigchel
Honored Contributor

Re: Create flag in load script

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

Re: Create flag in load script

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;

QlikCommunity_Thread_162541_Pic1.JPG

hope this helps

regards

Marco

stigchel
Honored Contributor

Re: Create flag in load script

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

Community Browser